Passing DTS Global Variables to Stored Procedures

Passing DTS Global Variables to Stored Procedures

Post by Garret » Sat, 08 Sep 2001 01:13:18



Here we go.
We have several TSQL stored procedures that require input parameters.  We
are using a DTS package as a driver procedure to execute this set of stored
procedures.  Currently, we are using the Execute SQL Task in DTS to begin
the stored procs.....and hard coding the parameters there.....but we would
like to have one place to edit the parameters rather than 1,2,3,4,5,6,
places etc.
Is it possible to pass one or more DTS global variables to a stored
procedure (TSQL) that is being executed within that particular package?  If
so how....VBscript???

thanks in advance

 
 
 

Passing DTS Global Variables to Stored Procedures

Post by Euan Garde » Sat, 08 Sep 2001 15:11:34


You can do it by using VBScript to re-write the SQL statement from an Ax
Script task that executes before the ExecSQL, or in SQL 2000 you can do it
via Parameters. If you use parameters try using the SQL Server OLE DB
Providers and also the SQL ODBC driver via the OLE DB Provider for ODBC.
Parameterised SP's are VERY sensitive to driver meta data.

--
Euan

Please respond directly to the newsgroup and not via e-mail.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.


Quote:> Here we go.
> We have several TSQL stored procedures that require input parameters.  We
> are using a DTS package as a driver procedure to execute this set of
stored
> procedures.  Currently, we are using the Execute SQL Task in DTS to begin
> the stored procs.....and hard coding the parameters there.....but we would
> like to have one place to edit the parameters rather than 1,2,3,4,5,6,
> places etc.
> Is it possible to pass one or more DTS global variables to a stored
> procedure (TSQL) that is being executed within that particular package?
If
> so how....VBscript???

> thanks in advance


 
 
 

1. how can I pass global variables from one extended stored procedure to another

My problem:
I need to process data during writing/reading MS SQL Server 7.0 using
extended stored procedures.
There are coommon variable for the connection wich I should use in the
extended stored procedures.
Many but not all connections should use the processing.

how can I pass global variables from one extended stored procedure to
another.
Distriction:
1. I should use this variable only in the same trhead(connection) it was
defined.
2. There is many threads(connections)

2. Can't add, update or delete on subscriber

3. Connection failed in Sql 6.5

4. Map stored procedure output parameter to DTS Global variable

5. Informix client on Linux?

6. Setting DTS package global variables from stored procedures?

7. ODBC excel and text drivers

8. DTS VB custom task reading a global variable and modifying a global variable

9. Passing variables from DTS to Stored Procedure

10. passing global variables between DTS packages

11. Passing String Global Variables to DTS via Vb.net

12. Passing Global Variables between DTS Packages