April 24, 2003 at 10:49 am
Hello.
I am having trouble executing a dynamically built stored procedure command.
I cannot get sp_exectutesql to run it.
It seems to expect a select statement or the like, not an actual call to execute a stored procedure.
It is for database administration purposes.
I have code running that builds the stored procedure call dynamically because it is being used to run the same stored procedure calls for different databases.
I have the following which doesn't work:
Execute @ExecSP
And tried
Execute sp_executesql @ExecSP
Where @ExecSP = @DBName + '.dbo.stp_Interface_Import_ReportData 1'
1 is a parameter that is being passed.
Please HELP!! 🙂
April 24, 2003 at 1:31 pm
What kind of error do you get? Can you run this with a hard-coded database name and it works fine?
Darren
Darren
April 25, 2003 at 1:51 am
Try the following
SET @ExecSP = 'EXEC ' + @DBName + '.dbo.stp_Interface_Import_ReportData 1'
Execute sp_executesql @ExecSP
April 25, 2003 at 6:24 am
When I Print @ExecSP
and then
Execute sp_executesql @ExecSP
I get the following:
Set lareal_v170.dbo.stp_Interface_Import_ReportData 1
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 43
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Thanks!
April 25, 2003 at 6:40 am
Your variable @ExecSP must be defined as nvarchar. I suspect you are defining it as varchar
April 25, 2003 at 6:41 am
The error is because @ExecSP has been declared as VARCHAR. Change the declaration to NVARCHAR.
sp_executesql can take only NVARCHAR data as parameter
April 25, 2003 at 6:47 am
Thanks for the nvarchar declaration comment.
It fixed the problem. I then had to take the 'Set ' command out of the statement
Thanks!!
The code then became:
Set @ExecSP = @DBName + '.dbo.stp_Interface_Import_ReportData 1'
Print @ExecSP
Execute sp_executesql @ExecSP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply