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:
>