Exec Proc with date parameter

Hi,
I have spent whole night to figure this out and right now i am hands up..
please help.
I have stored procedure (dsTPint_GLIntegrationRun) which have nested cursors
and cursors call external stored procedure to execute.
dsTPint_GLIntegrationRun have parameter of companyid which is database id. i
want to run stored procedure residing in the database which is equal to the
database passed as parameter.
Now one of the the parameters for the external store procedure is date, i am
passing date and keep getting on error incorrect syntax near '1'. I changed
the date parameter and supplied constant value for debugging and now i am
getting error cannot convert nvarchar into int. all the external stored
procedure looks fine, source table is hundered percent accurate, i am not
sure whats wrong. following is the syntax:
Exec ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + ' OUTPUT
, ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' + @.TrnxDate
+ ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period + ', ' +
@.Year + ', ''' + @.Currency + '''')
-- Exec dsTPint_GLIntegrationRun 'WSZM'
this Exec statement is producing the error and the parameter which is
producing the error is @.TrnxDate.
Thanks
DhariwalHave you tried replacing EXEC with PRINT to check for syntax errors?
PRINT ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
OUTPUT
, ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
@.TrnxDate
+ ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period +
', ' +
@.Year + ', ''' + @.Currency + '''')
Also, you may want to use sp_executeSQL instead; it handles paramaters
much better than building a string.|||Remember that you are building a character string for your SQL statement.
Consequently, all of the variables and expressions must be character types.
Also, because you are passing a string containing constants to EXEC, you
can't have output parameter values. You need to use sp_executesql to return
output values from dynamic SQL. Below is an example you can tweak to
specify your actual datatypes and variable names.
DECLARE @.SQL nvarchar(4000),
@.dbname sysname,
@.oErrString varchar(50),
@.NEXTJNLNBR int,
@.GPBatchNumber int,
@.TrnxDate datetime,
@.Reference int,
@.Ortrxnum int,
@.Period int,
@.Year int,
@.Currency int
SET @.dbname = N'MyDatabase'
SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
@.oErrString output,
@.NEXTJNLNBR output,
@.GPBatchNumber,
@.TrnxDate,
@.Reference,
@.Ortrxnum,
@.Period,
@.Year,
@.Currency'
EXEC sp_executesql @.SQL,
N'@.oErrString varchar(50) output,
@.NEXTJNLNBR int output,
@.GPBatchNumber int,
@.TrnxDate datetime,
@.Reference int,
@.Ortrxnum int,
@.Period int,
@.Year int,
@.Currency int',
@.oErrString OUTPUT,
@.NEXTJNLNBR OUTPUT,
@.GPBatchNumber = @.GPBatchNumber,
@.TrnxDate = @.TrnxDate,
@.Reference = @.Reference,
@.Ortrxnum = @.Ortrxnum,
@.Period = @.Period,
@.Year = @.Year,
@.Currency = @.Currency
Hope this helps.
Dan Guzman
SQL Server MVP
"K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
> Hi,
> I have spent whole night to figure this out and right now i am hands up..
> please help.
> I have stored procedure (dsTPint_GLIntegrationRun) which have nested
> cursors
> and cursors call external stored procedure to execute.
> dsTPint_GLIntegrationRun have parameter of companyid which is database id.
> i
> want to run stored procedure residing in the database which is equal to
> the
> database passed as parameter.
> Now one of the the parameters for the external store procedure is date, i
> am
> passing date and keep getting on error incorrect syntax near '1'. I
> changed
> the date parameter and supplied constant value for debugging and now i am
> getting error cannot convert nvarchar into int. all the external stored
> procedure looks fine, source table is hundered percent accurate, i am not
> sure whats wrong. following is the syntax:
> Exec ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
> OUTPUT
> , ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
> @.TrnxDate
> + ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period + ', '
> +
> @.Year + ', ''' + @.Currency + '''')
> -- Exec dsTPint_GLIntegrationRun 'WSZM'
> this Exec statement is producing the error and the parameter which is
> producing the error is @.TrnxDate.
> Thanks
> Dhariwal|||Hi Stu,
Thanks for response.
I did try Print to see the statement, PRINT produces error that int cannot
be converted to string as there are parameters with int , date and varchar.
I will check sp_executeSQL to see if that helps.
Thanks
Dhariwal
"Stu" wrote:

> Have you tried replacing EXEC with PRINT to check for syntax errors?
> PRINT ('Exec ' + @.dbname + '..daiGLHeaderInsert ' + @.oErrString + '
> OUTPUT
> , ' + @.NEXTJNLNBR + ' OUTPUT , ''' + @.GPBatchNumber + ''' , ''' +
> @.TrnxDate
> + ''' , ''' + @.Reference + ''', ''' + @.Ortrxnum + ''', ' + @.Period +
> ', ' +
> @.Year + ', ''' + @.Currency + '''')
> Also, you may want to use sp_executeSQL instead; it handles paramaters
> much better than building a string.
>|||Dan and STU , you guys are stars. sp_executesql did the magic.
Thanks for your precious time, i really appreciated your help.
regards,
Dhariwal
"Dan Guzman" wrote:

> Remember that you are building a character string for your SQL statement.
> Consequently, all of the variables and expressions must be character types
.
> Also, because you are passing a string containing constants to EXEC, you
> can't have output parameter values. You need to use sp_executesql to retu
rn
> output values from dynamic SQL. Below is an example you can tweak to
> specify your actual datatypes and variable names.
> DECLARE @.SQL nvarchar(4000),
> @.dbname sysname,
> @.oErrString varchar(50),
> @.NEXTJNLNBR int,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int
> SET @.dbname = N'MyDatabase'
> SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
> @.oErrString output,
> @.NEXTJNLNBR output,
> @.GPBatchNumber,
> @.TrnxDate,
> @.Reference,
> @.Ortrxnum,
> @.Period,
> @.Year,
> @.Currency'
> EXEC sp_executesql @.SQL,
> N'@.oErrString varchar(50) output,
> @.NEXTJNLNBR int output,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int',
> @.oErrString OUTPUT,
> @.NEXTJNLNBR OUTPUT,
> @.GPBatchNumber = @.GPBatchNumber,
> @.TrnxDate = @.TrnxDate,
> @.Reference = @.Reference,
> @.Ortrxnum = @.Ortrxnum,
> @.Period = @.Period,
> @.Year = @.Year,
> @.Currency = @.Currency
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
> news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
>
>|||Hi Dan,
One small issue still.
I cant retrieve values from OUTPUT parameters, they are NULL.
Following is the syntax:
-- build the exec query for the database specified in input parameter
SET @.SQLString = ('Exec ' + @.dbname + '..daiGLHeaderInsert @.oErrString
OUTPUT, @.NEXTJNLNBR OUTPUT, @.GPBatchNumber, @.TrnxDate, @.Reference,
@.Ortrxnum, @.Period, @.Year, @.Currency')
SET @.ParmDefinition = N'@.oErrString varchar(255) OUTPUT, @.NEXTJNLNBR int
OUTPUT, @.GPBatchNumber char(15), @.TrnxDate datetime, @.Reference char(31),
@.Ortrxnum char(13), @.Period smallint, @.Year smallint, @.Currency char(10) '
execute sp_executesql @.SQLString , @.ParmDefinition ,
@.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT , @.GPBatchNumber = @.GPBatchNumber
, @.TrnxDate = @.TrnxDate , @.Reference = @.Reference , @.Ortrxnum = @.Ortrxnum ,
@.Period = @.Period , @.Year = @.Year , @.Currency = @.Currency
PRINT @.NEXTJNLNBR
(This print returns nothing at all)
Also i noted that i had to declare @.NEXTJNLNBR before the above code else i
get error that varialbe needs to be declared.
Following is the PRINT of the above statement:
Exec WSZM..daiGLHeaderInsert @.oErrString OUTPUT, @.NEXTJNLNBR OUTPUT,
@.GPBatchNumber, @.TrnxDate, @.Reference, @.Ortrxnum, @.Period, @.Year, @.Currency
Thanks
Dhariwal
"Dan Guzman" wrote:

> Remember that you are building a character string for your SQL statement.
> Consequently, all of the variables and expressions must be character types
.
> Also, because you are passing a string containing constants to EXEC, you
> can't have output parameter values. You need to use sp_executesql to retu
rn
> output values from dynamic SQL. Below is an example you can tweak to
> specify your actual datatypes and variable names.
> DECLARE @.SQL nvarchar(4000),
> @.dbname sysname,
> @.oErrString varchar(50),
> @.NEXTJNLNBR int,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int
> SET @.dbname = N'MyDatabase'
> SET @.SQL = N'EXEC ' + @.dbname + '..daiGLHeaderInsert
> @.oErrString output,
> @.NEXTJNLNBR output,
> @.GPBatchNumber,
> @.TrnxDate,
> @.Reference,
> @.Ortrxnum,
> @.Period,
> @.Year,
> @.Currency'
> EXEC sp_executesql @.SQL,
> N'@.oErrString varchar(50) output,
> @.NEXTJNLNBR int output,
> @.GPBatchNumber int,
> @.TrnxDate datetime,
> @.Reference int,
> @.Ortrxnum int,
> @.Period int,
> @.Year int,
> @.Currency int',
> @.oErrString OUTPUT,
> @.NEXTJNLNBR OUTPUT,
> @.GPBatchNumber = @.GPBatchNumber,
> @.TrnxDate = @.TrnxDate,
> @.Reference = @.Reference,
> @.Ortrxnum = @.Ortrxnum,
> @.Period = @.Period,
> @.Year = @.Year,
> @.Currency = @.Currency
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "K M Dhariwal" <KMDhariwal@.discussions.microsoft.com> wrote in message
> news:B3EB5745-92AA-4D21-905D-30CEEFF4656E@.microsoft.com...
>
>|||Dhariwal,
Your declaration of @.ParmDefinition is probably too short to hold the
complete parameter definition.
before you execute sp_executesql, try PRINT @.ParmDefinition to
see, or change its definition to
DECLARE @.ParmDefinition(1000)
for example.
Steve Kass
Drew University
K M Dhariwal wrote:
>Hi Dan,
>One small issue still.
>I cant retrieve values from OUTPUT parameters, they are NULL.
>Following is the syntax:
> -- build the exec query for the database specified in input parameter
> SET @.SQLString = ('Exec ' + @.dbname + '..daiGLHeaderInsert @.oErrString
>OUTPUT, @.NEXTJNLNBR OUTPUT, @.GPBatchNumber, @.TrnxDate, @.Reference,
>@.Ortrxnum, @.Period, @.Year, @.Currency')
> SET @.ParmDefinition = N'@.oErrString varchar(255) OUTPUT, @.NEXTJNLNBR int
>OUTPUT, @.GPBatchNumber char(15), @.TrnxDate datetime, @.Reference char(31),
>@.Ortrxnum char(13), @.Period smallint, @.Year smallint, @.Currency char(10) '
> execute sp_executesql @.SQLString , @.ParmDefinition ,
> @.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT , @.GPBatchNumber = @.GPBatchNumbe
r
>, @.TrnxDate = @.TrnxDate , @.Reference = @.Reference , @.Ortrxnum = @.Ortrxnum ,
>@.Period = @.Period , @.Year = @.Year , @.Currency = @.Currency
>PRINT @.NEXTJNLNBR
>(This print returns nothing at all)
>Also i noted that i had to declare @.NEXTJNLNBR before the above code else
i
>get error that varialbe needs to be declared.
>Following is the PRINT of the above statement:
>Exec WSZM..daiGLHeaderInsert @.oErrString OUTPUT, @.NEXTJNLNBR OUTPUT,
>@.GPBatchNumber, @.TrnxDate, @.Reference, @.Ortrxnum, @.Period, @.Year, @.Currenc
y
>Thanks
>Dhariwal
>
>"Dan Guzman" wrote:
>
>|||>>@.oErrString OUTPUT , @.NEXTJNLNBR OUTPUT
try this
1. you should declare all out parameters twice once in exec scope(param def)
and other in your calling code and then
@.oErrString = @.oErrString,@.NEXTJNLNBR = @.NEXTJNLNBR to assign the values
print @.NEXTJNLNBR
Regards
R.D
"Steve Kass" wrote:

> Dhariwal,
> Your declaration of @.ParmDefinition is probably too short to hold the
> complete parameter definition.
> before you execute sp_executesql, try PRINT @.ParmDefinition to
> see, or change its definition to
> DECLARE @.ParmDefinition(1000)
> for example.
> Steve Kass
> Drew University
> K M Dhariwal wrote:
>
>|||Hi Steve & RD.
The problem was in the logic of external proc and the statement wasn't even
getting to the line where it must populate the value of output parameter.
Thanks for the help guys.
Regrads,
Dhariwal
"R.D" wrote:
> try this
> 1. you should declare all out parameters twice once in exec scope(param de
f)
> and other in your calling code and then
> @.oErrString = @.oErrString,@.NEXTJNLNBR = @.NEXTJNLNBR to assign the values
> print @.NEXTJNLNBR
> Regards
> R.D
> "Steve Kass" wrote:
>

EXEC date problem

Hello all,
I have a standard stored procedure that contains the following code:

SET @.SQL = 'SELECT id, building, firstname, lastname, closed, assignto, vandalism, entered_by, phone, roomno, status, dateentered, date_closed AS DATECLOSED FROM requests '
SET @.SQL = @.SQL + 'WHERE building_no IN (' + @.BUILDINGLIST + ') AND STATUS_NO = ' + @.STATUS + ' '

Works fine, but my dates come back unformated i want to replace the above:

dateentered and date_closed

with

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED
CONVERT(varchar(30), date_closed, 101) AS DATECLOSED

but, when i do, i receive the following error:

ERROR CLOSE TO AS

Does anyone have any ideas? Thanks in advance.

Richard M.what's it look like when you've already replaced them?

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED

also, dateentered appears to be a field that doesn't exist.

Excluding weekend dates from calculated data pull

I have a business need to display some date specific data to a selcect group
of users 3 bussiness days before everyone else is allowed to see it.
THere are many fields I will be displaying across many tables. The date I am
using is ONLY in the primary table (meaning I don't have to include or
exclude data based on a date match from the other tables).
What I need to know is if there is a way in the SQL Query to not count the
weekend days as part of the 3 bussiness days. My query right now uses "WHERE
datafield < (GetDate + 5)". But this lets the users see data for the week's
Friday on the week's Monday. Not acceptable. If I only use "WHERE datafield <
(GetDate + 3) Then data to been displayed on This week's Tuesday can not be
seen on the previous Friday. Also Not Acceptable.
Any ideas?
Thanks.
Carrie E. Wells
Web Architect/Developer
Oh, and if Ihave this posted int eh wrong place tell me where to put it an I
move it.
On Wed, 25 May 2005 08:43:25 -0700, Wells wrote:
(snip)
>What I need to know is if there is a way in the SQL Query to not count the
>weekend days as part of the 3 bussiness days. My query right now uses "WHERE
>datafield < (GetDate + 5)". But this lets the users see data for the week's
>Friday on the week's Monday. Not acceptable. If I only use "WHERE datafield <
>(GetDate + 3) Then data to been displayed on This week's Tuesday can not be
>seen on the previous Friday. Also Not Acceptable.
Hi Carrie,
The simple, quick and dirty solution would be to use a CASE expression
to add either 3 or 5 to the current date, depending on the result of
DATEPART(day, GetDate()). But that would not take public holidays and
company holidays into account.
The best solution is to use a calendar table. You'll have to create it
once; after that, you can use it in this and many other situations. How
to make a calendar table, and many possible usees, is described at this
site: http://www.aspfaq.com/show.asp?id=2519

>Oh, and if Ihave this posted int eh wrong place tell me where to put it an I
>move it.
I don't think you can move posts on Usenet :-)
Anyway, this group is actually intended for MSEQ (Microsoft English
Query). In practice, nobody ever posts anything about MSEQ, but the
group does catch some stray questions about SQL Server queries.
A better place for this kind of questions is the programming group at
microsoft.public.sqlserver.programming. That group is frequented by many
more experts than this one.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Since the site is 24/7 then that should be ok. The whole reason for 3 days
was to work around the schedules of our foriegn customers, who of course
don't have the same holidays. This issue was about certian Key Customers
getting the data first but not before the company rules said they could.
Thanks for the solution. I never even thought about using a Case statement
and this has been driving me crazy for two weeks. :-)
Thanks Again,
Carrie Wells
Carrie E. Wells
Web Architect/Developer
"Hugo Kornelis" wrote:

> On Wed, 25 May 2005 08:43:25 -0700, Wells wrote:
> (snip)
> Hi Carrie,
> The simple, quick and dirty solution would be to use a CASE expression
> to add either 3 or 5 to the current date, depending on the result of
> DATEPART(day, GetDate()). But that would not take public holidays and
> company holidays into account.
> The best solution is to use a calendar table. You'll have to create it
> once; after that, you can use it in this and many other situations. How
> to make a calendar table, and many possible usees, is described at this
> site: http://www.aspfaq.com/show.asp?id=2519
>
> I don't think you can move posts on Usenet :-)
> Anyway, this group is actually intended for MSEQ (Microsoft English
> Query). In practice, nobody ever posts anything about MSEQ, but the
> group does catch some stray questions about SQL Server queries.
> A better place for this kind of questions is the programming group at
> microsoft.public.sqlserver.programming. That group is frequented by many
> more experts than this one.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Exclude Null Dates

I can't seem to figure out how to exclude records with a null in a date
field as part of a filter expression. I've tried all of the following:
!=<blank>
!= System.DBNull
!=DBNull
!=""
!=''Try using IsNothing, like:
=IIF(IsNothing(Fields!MyDateField.Value), 1, 0)
Kaisa M. Lindahl Lervik
"Cindy Mikeworth" <CindyMikeworth@.newsgroups.nospam> wrote in message
news:utiawBl6GHA.4232@.TK2MSFTNGP02.phx.gbl...
>I can't seem to figure out how to exclude records with a null in a date
>field as part of a filter expression. I've tried all of the following:
> !=<blank>
> != System.DBNull
> !=DBNull
> !=""
> !=''
>|||Awesome! That did the trick. Thanks for the reference tips as well.

EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:
> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > Hi,
> >
> > I'm trying to run a sproc that was created on SQL server and has two date
> > parameters. I can run the sproc easily enough with fixed values but it
> > doesn't work with parameters as the sproc can't be displayed
> graphically?!?!
> >
> > I have seen that the suggested solution is to create a view but as this
> > query uses group by, using the where clause in a view to specify the
> > parameteres will not give the correct result set.
> >
> > This is part of the SQL from the sproc.....
> >
> > SELECT SG.SchemeName as SchemeName,
> > 'Fail' as Type,
> > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> END),0)
> > as Value
> > FROM Quote Q
> > INNER JOIN SchemeGroup SG
> > ON Q.SchemeGroupID = SG.ID
> > INNER JOIN SubScheme SS
> > ON Q.ID = SS.QuoteID
> > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > GROUP BY SG.SchemeName
> > ORDER BY SG.SchemeName,Type desc
> >
> > Any help or ideas would be appreciated.
> >
> > Thanks,
> >
> > Ruz
> >
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"
>
> Cheers,
> "Uri Dimant" wrote:
> > Ruz
> > CREATE PROC myProc
> > @.SDate DATETIME,
> > @.FDate DATETIME
> > AS
> > --Your code here
> >
> >
> > If not sure I understand you
> > What does it mean?
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> >
> >
> >
> > "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> > news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> > > Hi,
> > >
> > > I'm trying to run a sproc that was created on SQL server and has two
date
> > > parameters. I can run the sproc easily enough with fixed values but it
> > > doesn't work with parameters as the sproc can't be displayed
> > graphically?!?!
> > >
> > > I have seen that the suggested solution is to create a view but as
this
> > > query uses group by, using the where clause in a view to specify the
> > > parameteres will not give the correct result set.
> > >
> > > This is part of the SQL from the sproc.....
> > >
> > > SELECT SG.SchemeName as SchemeName,
> > > 'Fail' as Type,
> > > COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> > > COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
> > END),0)
> > > as Value
> > > FROM Quote Q
> > > INNER JOIN SchemeGroup SG
> > > ON Q.SchemeGroupID = SG.ID
> > > INNER JOIN SubScheme SS
> > > ON Q.ID = SS.QuoteID
> > > WHERE lql.dbo.isPolicy(Q.ID) = 0
> > > AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> > > GROUP BY SG.SchemeName
> > > ORDER BY SG.SchemeName,Type desc
> > >
> > > Any help or ideas would be appreciated.
> > >
> > > Thanks,
> > >
> > > Ruz
> > >
> >
> >
> >

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel group
Nothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:

> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel groupNothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:
> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel to MS SQL

Hai all,
I want to export the data from Excel sheet to SQL database. In the Excel sheet one column contains the date value and non date like 0 and blank. I want to transfer this to my table by changing the format to dd/mm/yyyy.

If I open and format the column to date, and if I export then in the table i am getting different formats ( based on the client machines Date format , The column in the table is Varchar becaust the Excel sheet column will contain date and other non date like 0 , blank etc )

Now how can I export the Excel column that contains date , 0 and blanks to the table in the database with date format dd/mm/yyyy and null for non date values?

Thanks
NarayanaswamyYou should import your data as VARCHAR in a temporary table, and transform it in a second step.|||Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy|||Yeah, Excel is great, but not for storing data.

I would take another approach, and would include some VBA code into your workbooks to transform your sheets first into the proper format before exporting them. Your could also consider to export them by VBA.|||Originally posted by Narayanaswamy
Hai ,
I tried that also, but the problem is
If In the excel sheet first field is a text or 0 then All thetext values are transfered to the Table but the date fields are transfered as NULL.
If the first row in the excel is Date then All the date are transferred properly and the text and 0 are transfered as NULL.

But the date transfed is of various type ( as per the client machine Date format how can i convert it to same format?)

Can you help me in solving this problem?

Thanks

Narayanaswamy

I did have the same problem with importing excel data to MSSQL.
After some fighting I just imported data to Access and then to MSSQL.
It was needed to do not often. :)

Excel Time-Series Addin Problem

Have an Excel Spreadsheet with two columns and 52 rows (retail sales)

Date Qty

1 5

2 8

etc

Tried to run the above Addin. Error message:

"Session mining object (...) can not be created on this instance."

Any ideas?

Thanks,

Sergei.

You need to set the DataMinnig\AllowSessionMiningModels property to true on your Analysis Services server. Run SQL Server Management Studio, right click on the server in the object explorer, and select properties to set it.