Stored procedure parameter and global variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Stored procedure parameter and global variables

Author

Topic

SqlServerUser
Starting Member

1 Post

Posted - 2004-08-17 : 16:40:36

Execute SQL Task Error - Stored procedure input parameter and global variables :

In a execute SQL task after processing a set of records (using a cursor), the final statement sends an e-mail. I have SET NOCOUNT ON in the beginning of the task.

The following email statement works fine:

EXEC master..xp_sendmail
@recipients = 'user@address.com',
@subject = 'ABC Report',
@message = 'Contents of ABC table',
@query = 'SELECT * FROM mydb.dbo.ABC',
@attach_results = 'TRUE', @width = 250

But I want to use global variables as parameter values and tried to use ? (parameter marker) as below

EXEC master..xp_sendmail
@recipients = 'user@address.com',
@subject = ?,
@message = 'Contents of ABC table',
@query = 'SELECT * FROM mydb.dbo.ABC',
@attach_results = 'TRUE', @width = 250

When I clicked the "Parameters" button to assign a global variable, I always get an error message:

Error Source : Microsoft OLE DB Provider for SQL Server
Syntax error or access violation

I have tried using paranthesis also (@subject = ?).
Is there any solution to this problem? Or is this a bug in SQL Server?

Thanks a lot.

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-08-18 : 12:23:00

1. Create package global variable gv_SQL
2. Create 3 new Tasks: 1 ActiveX, Dynamic Property, Execute SQL (add the following sql statement - SELECT NULL)
3. Create On Success precedence from ActiveX Task to Dynamic Property Task and from the Dynamic Property Task to the Execute SQL task.
4. Insert logic to build the dynamic query in the ActiveX task, example:
'replace gv_Subject with your global variable name

Function Main()
Dim sSubject
sSubject = DTSGlobalVariables("gv_Subject").Value

DTSGlobalVariables("gv_SQL") = "EXEC master..xp_sendmail @recipients ='user@address.com', @subject = " & sSubject & " ,@message = 'Contents of ABC table', @query = 'SELECT * FROM mydb.dbo.ABC', @attach_results = 'TRUE', width = 250"

Main = DTSTaskExecResult_Success
End Function

5. Double click DYnamic Property Task, Click Add Button, Expand where it says Tasks on the left, find the Execute SQL task added in step 2(above), Find the property SQL Statement and then double click default value, under Source select Global Variable, Then select gv_SQL from the Variable dropdown listbox.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources