paulfryer
asked on
How to Execute Linked Stored Procedures in a DTS
I am a little unfamiliar with designing DTS packages, but what I want to do is visually display 5 stored procedures linked together and save that as a “Job” or “Package”. I then want to be able to call the “Job/Package” from a stored procedure.
I know I can use DTSRUN to call the package. I tried this with a very simple package that consisted of a connection and a select statement like this:
SELECT ‘this is a test’
The output I got when I ran the package from Query Analyzer was:
output
--------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_
DTSRun: Package execution complete.
NULL
(6 row(s) affected)
Why didn’t it print ‘this is a test’?
Also how do I pass input prameters to the package and get output prameter back from it via Query Analyzer?
I know I can use DTSRUN to call the package. I tried this with a very simple package that consisted of a connection and a select statement like this:
SELECT ‘this is a test’
The output I got when I ran the package from Query Analyzer was:
output
--------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_
DTSRun: Package execution complete.
NULL
(6 row(s) affected)
Why didn’t it print ‘this is a test’?
Also how do I pass input prameters to the package and get output prameter back from it via Query Analyzer?
outputs (i.e. results from select statements) from stored procedures etc do not cascade.
If you call procedure A which in turn calls procedure B, you will only see the outputs from procedure A, the outputs from procedure B will be lost.(unless procB does a print of somethign, which will still be seen, if you happen to be useing something like QA)
The results of select statements are returned not to the output screen, but to the calling process, eg in this case procedure B returns it's outputs to procedure A. PRocedure A does nothing with these results, so they are 'lost'. It is only when you call procedure B directly from an application such as QA, where the application catches the returned record set, and displays it on the screen that you actually see the results.
There is no simple way to concatenate the results of multiple stored procs together, this is the job of your application layer, to call each procedure in turn, and then catch the results of each.
Note, the main purpose of DTS is, as the name suggests, Data Transformation. It is intended to DO something, rathter than for returning record sets. One feature of DTS packages is that you can execute SQL commands, but the reson you would do this is to have the procedure do somehting, like add new data / manipulate data in some way, rather than to return a result set.
If all you want to do is return record sets, then you should not be thinking about using DTS packages.
If you call procedure A which in turn calls procedure B, you will only see the outputs from procedure A, the outputs from procedure B will be lost.(unless procB does a print of somethign, which will still be seen, if you happen to be useing something like QA)
The results of select statements are returned not to the output screen, but to the calling process, eg in this case procedure B returns it's outputs to procedure A. PRocedure A does nothing with these results, so they are 'lost'. It is only when you call procedure B directly from an application such as QA, where the application catches the returned record set, and displays it on the screen that you actually see the results.
There is no simple way to concatenate the results of multiple stored procs together, this is the job of your application layer, to call each procedure in turn, and then catch the results of each.
Note, the main purpose of DTS is, as the name suggests, Data Transformation. It is intended to DO something, rathter than for returning record sets. One feature of DTS packages is that you can execute SQL commands, but the reson you would do this is to have the procedure do somehting, like add new data / manipulate data in some way, rather than to return a result set.
If all you want to do is return record sets, then you should not be thinking about using DTS packages.
ASKER
acperkins -
The task object I have when I design the DTS Package is a “Execute SQL Task”. When I go to the properties of it, I can enter a SQL statement. There is a button called “Parameters”, when I click I can add Global Variables. What I don’t understand is how I reference the Global Variables in the SQL of the Task. I guess I still don’t understand how to map the input variables/global variables. Could you please explain that part in more detail?
The task object I have when I design the DTS Package is a “Execute SQL Task”. When I go to the properties of it, I can enter a SQL statement. There is a button called “Parameters”, when I click I can add Global Variables. What I don’t understand is how I reference the Global Variables in the SQL of the Task. I guess I still don’t understand how to map the input variables/global variables. Could you please explain that part in more detail?
ASKER
BillAn1 -
The reason I am considering using DTS packages is because I need a way to visually see what is going on. Some of my processes require 10 stored procedures to run at once. It gets very confusing to try to open 10 individual windows of code and go back and forth to see what input variable must match what output variable. What I need is a way to visually see/design the process with stored procedures represented as visual objects that can take information in and spit information out. I have not found a program that can do this with TSQL stored procedures so DTS is the closes thing I can find. I am open to sujestions for other visual TSQL development programs however.
The reason I am considering using DTS packages is because I need a way to visually see what is going on. Some of my processes require 10 stored procedures to run at once. It gets very confusing to try to open 10 individual windows of code and go back and forth to see what input variable must match what output variable. What I need is a way to visually see/design the process with stored procedures represented as visual objects that can take information in and spit information out. I have not found a program that can do this with TSQL stored procedures so DTS is the closes thing I can find. I am open to sujestions for other visual TSQL development programs however.
ASKER
Apparently I can map global variables to input parameters. How do I specify an input parameter?
>>Apparently I can map global variables to input parameters.<<
And assign output parameters to global variables.
>>How do I specify an input parameter?<<
With a "?", as in:
Select * from Customers where Country = ?
And assign output parameters to global variables.
>>How do I specify an input parameter?<<
With a "?", as in:
Select * from Customers where Country = ?
ASKER
OK lets consider the folowing SQL:
DECLARE
@UserName NVARCHAR(100)
SELECT *
FROM USERS
WHERE (NAME = @UserName)
Now I think @UserName is the input parameter. When I click on the "Parameters.." button in the Execute SQL Task Properties box, I can't select anything where it says "Parameter Mapping". Its like the porgram dosn't recognize @UserName as an input parameter. I still don't know how to "MAP" the parameters.
DECLARE
@UserName NVARCHAR(100)
SELECT *
FROM USERS
WHERE (NAME = @UserName)
Now I think @UserName is the input parameter. When I click on the "Parameters.." button in the Execute SQL Task Properties box, I can't select anything where it says "Parameter Mapping". Its like the porgram dosn't recognize @UserName as an input parameter. I still don't know how to "MAP" the parameters.
ASKER
OK, I see - I didn't know you where being literal. Use a "?". Thanks, I'll see if I can get somewhere with that.
ASKER
What is the difference in the Output Parameter Types: (None, Row Value, and Rowset)?
ASKER
Also how do you get output parameters out of the package? I mean how do I have the package do something, then get the values out of it so they can be referenced from my code locally.
>>Also how do you get output parameters out of the package?<<
For example, you want to get FirstName and LastName given UserName than your SQL statement would look like this:
Select FirstName, LastName Where UserName = ?
1. Click on Output Parameters.
2. Select RowValue
3. Assign Global variables.
For example, you want to get FirstName and LastName given UserName than your SQL statement would look like this:
Select FirstName, LastName Where UserName = ?
1. Click on Output Parameters.
2. Select RowValue
3. Assign Global variables.
ASKER
buy how do you actually extract the output variable in the DTRUN sql statement?
>>buy how do you actually extract the output variable in the DTRUN sql statement?<<
Not following you here. DTSRun allows you to pas in global variables on the command line. Is that what you are referring to?
Not following you here. DTSRun allows you to pas in global variables on the command line. Is that what you are referring to?
ASKER
yes sort of. I am referencing DTSRun from a stored procedure. How do I get the output parameters from the DTS package back into my stored procedure?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Because the package ran on the server.
>>Also how do I pass input prameters to the package <<
Declare global variables and pass them using /A if you are using DTSRun.
>>and get output prameter back from it via Query Analyzer<<
You can Insert it into a table. Or use ActiveX and use a MessageBox. Of course, this last cannot be used when the package is run unattended.