April 28, 2003 at 7:56 pm
HI there.
I've been writing a store procedure that needs to return a value to a DTS package.
I've coded RETURN statement in the store procedure, however I don't know how to get the value.
Do I have to create a global variable from the package and assign it from the Store procedure ?
Any help is very welcome.
Thanks,
SQL server beginner
April 28, 2003 at 8:04 pm
You could configure an output parameter in your stored procedure - see (watch for wrap of url):
http://www.sqlservercentral.com/columnists/sburke/introductiontostoredprocedures.asp
Then create a global variable in your package. In your Execute SQL Task, choose parameters and assign the value of the stored procedure output parameter to the global variable. Then in your other tasks in the package you can reference this global variable to pass the variable's value.
hth,
Michael
Michael Weiss
Michael Weiss
April 28, 2003 at 9:16 pm
Michael
Thank you very much for your help. It's helped me to clarify some SQL server concepts.
One more question if you don't mind.
When I detect an error in the store procedure (for example: insert an invalid order_id), I'm using @@ERROR to track this kind of error.
Is there a way to stop my DTS package by passing this value as OUTPUT variable to the package?
Regards
Lou
April 28, 2003 at 9:58 pm
One way to do this would be to place an ActiveX task after your ExecuteSQL task. In your ActiveX task, check the value of your global variable...if it is an error value, terminate the ActiveX task with failure and follow your ActiveX task with an 'on success' workflow to the next task...this will cause the package itself to stop after the ActiveX task completes. You will probably want to give some thought to your tasks that participate in transactions, etc. and make sure that failing the package at that point is what you want.
Example ActiveX script:
Function Main()
Dim myvariable
myvariable = DTSGlobalVariables("myglobalvariable").Value
If myvariable = somevalueherethatyouknowtobeanerror Then
Main = DTSTaskExecResult_Failure
ELSE
Main = DTSTaskExecResult_Success
End If
End Function
hth,
Michael
Michael Weiss
Michael Weiss
April 28, 2003 at 10:20 pm
Master Michael.
Thank you very much for your help.
I'll try that tomorrow.
have a good one
Lou
SQl server beginner
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply