April 26, 2005 at 1:41 pm
In an ExecuteSQL task I call a stored procedure. If I raise an error in that stored procedure (with the RaisError function), and then execute the ExecuteSQL task, the SQL Error Message will be displayed when the ExecuteSQL task fails.
My question is, if I instantiate that ExecuteSQL task as an object in an ActiveScript task, and then call/execute that ExecuteSQL object, how can I access the returned error message in the ActiveScript task code?
Set objPackage = DTSGlobalVariables.Parent
'Instantiate the task and step
Set objTask_ExecuteSQLTask = objPackage.Tasks("DTSTask_DTSExecuteSQLTask_1")
Set objStep_ExecuteSQLTask = objPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
'Call the Execute SQL Task step
objStep_ExecuteSQLTask.Execute
'Check for step failure
If objStep_ExecuteSQLTask.ExecutionResult = 1 Then 'Step Failed
strErrorMessage = objStep_ExecuteSQLTask.?????????????
End If
Any help you can give will be greatly appreciated. Thanks.
April 29, 2005 at 8:00 am
This was removed by the editor as SPAM
May 1, 2005 at 10:51 am
If you use the Command object, you can look at the @RETURN_VALUE parameter after execution. You may need to issue a oCommand.Parameters.Refresh prior to execute method.
If @RETURN_VALUE is 0, the command was successful. Else, trap for the error.
-Mike Gercevich
May 1, 2005 at 10:55 am
If you insist on using the EXECSQL task, You'll have to define an input global variable and use the syntax below:
EXEC dbo.mystored_procedure @RETURN_VALUE = ? OUTPUT
The global Variable will be set to the value after it completes. I still think the Active-X script gives you more control.
-Mike Gercevich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply