Passing Global Variable to XP_SendMail Proc

Question

Passing Global Variable to XP_SendMail Proc

cocr

SSC Enthusiast

Points: 105
More actions
October 21, 2003 at 3:28 pm

#106738

Does anyone know how I can pass the value of a DTS Global Variable into the @message parameter of the xp_sendmail stored procedure (I am using the Execute SQL Task to run this stored procedure within my DTS Package)?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47795 More actions · Answer 1

Create a stored procedure that calls xp_sendmail and execute it instead of directly calling xp_sendmail. Then use the ? character to specify a parameter in the SQL statement.

eg: EXEC my_sendmail_proc @message= ?

Then click the parameters button and assign the global variable.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

cocr SSC Enthusiast Points: 105 More actions · Answer 2

Thanks for the response but I actually implemented a solution I found at http://sqldts.com

For those interested...

(1) Created a dummy Select statement with a parameter

(example: Select * From myTable Where myColumn = ?).

(2) Mapped my Global Variable to Parameter 1.

(3) Clicked Ok, etc to commit the changes to the Execute SQL task

(4) Entered the Disconneted Edit mode

(5) Chose the Execute SQL Task for this under the Tasks Group

(5) Pasted in the 'real' sql statement into the SQLStatement Property (example: EXEC master..xp_sendmail @recipients = 'My Distribution List',

@subject = 'My Subject', @message = ?)

philcart SSC-Forever Points: 47795 More actions · Answer 3

Whle that is a workable solution, I don't prefer to go that path myself. It means that if you ever want to edit the SQL statement, maybe to add another parameter, you have to go into disconnected edit. If you create a stored procedure that calls xp_sendmail you don't need to go anywhere near disconnected edit. Something to bear in mind when you have another person, that doesn't know about disconnected edit, trying to modify your DTS package in the future.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface