DTS - Execute SQL Task - Using Parameters
-
Hey everyone,
I can see how to use global variables as parameters when I have a simple sql statement such as:
Select * from mytable where myID=?
...but I can not for the life of me get parameters to work in either a variable assignment or as a parameter for a stored procedure.
I am working with Gert Draper's xp_smtp_sendmail procedure in an Execute SQL Task as follows:
/******************************************/
Declare @myString nvarchar(4000)
Declare @strTo nvarchar(4000)
Declare @strFrom nvarchar(4000)
Declare @strFromName nvarchar(4000)
Declare @strServer nvarchar(4000)
Declare @strMessage nvarchar(4000)
Declare @strSubject nvarchar(4000)
Set @strTo = N'me@test.com'
Set @strFrom = N'me@test.com'
Set @strFromName = N'Some Person'
Set @strServer = N'LOCALHOST'
Set @strMessage = N'Successfully completed execution. ' + cast(getDate() as nvarchar)
Set @strSubject = N'The Subject'
Set @myString = N'exec master.dbo.xp_smtp_sendmail @FROM=N''' + @strFrom +
''', @From_Name=N''' + @strFromName +
''', @TO=N''' + @strTo +
''', @server=N''' + @strServer +
''', @Message=N''' + @strMessage +
''', @Subject=N''' + @strSubject +
''''
execute sp_executesql @myString
/******************************************/
As it is above, this task works exactly as expected and properly sends the message. However, If I try to set one of the variable assignments using a parameter I get an error message (Syntax Error or Access Violation). For example:
Set @strFrom = ?
I have also tried Select @strFrom = ?
I guess I don't understand why this usage is so different than that of the simple query given above. Has anyone here had any experience with this? Is there something I am missing or is there a workaround that I can use? Is there not a way to get members of DTSGlobalVariables into an Execute SQL Task?
Thanks!
Sincerely,
Dan B
-
Why are you using dynamic SQL?
what is the problem with this:
exec master.dbo.xp_smtp_sendmail @FROM= ?, @From_Name= ? , @TO= ?, @server= ?, @Message= ?, @Subject= ?
just make sure you map all parameters through the GUI
hth
* Noel -
Thanks for the reply Noel,
I actually started with that, but it doesn't work that way either.
I was using dynamic sql to see if that might provide a solution.
Update-
I am not sure why, but first evidence seems to indicate that parameters can not be passed to xp_XYZ procedures. I tried a new execute sql task as follows:
exec master.dbo.xp_msver @optname = ?
Result - Error
But, when I wrapped xp_msver in a new procedure:
********************
create procedure sp_msverTEST
(
@optionname varchar(40)
)
as
exec master.dbo.xp_msver @optionname
*********************
...and then run it:
exec master.dbo.sp_msverTEST @optionname = ?
(? is set to FileDescription in package global variables)
Result - Success
It works. Very strange.
I will test some more and post what I find...
Sincerely,
Dan B
-
You can't assign parameters for extended stored procedures the same way as normal procedures. I think its something to do with retrieving the metadata.
However you can work around it by using Disconnected Edit, or a better option would be to use a wrapper stored procedure as demonstrated with your xp_msver example.
--------------------
Colt 45 - the original point and click interface
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply