September 28, 2005 at 7:36 am
I have an update statement i run at the outset of my dts process. Is there a way to raise an error if this update statement doesn't update a record.. Basically returning zero rows? I don't want the package to run any further if this occurs. Thanks.
September 28, 2005 at 10:44 am
The only way I can think of achieving this is to use a global variable of an output type.
However, the problem you have is that you can only have a single output global variable and the update statement is the value assigned to it as it is the first statement to run. In this case null is always returned.
You cannot do a select @@rowcount into another ouput global variable and use that value to see if any updates ocurred.
The best thing to do is to create a stored procedure and at the end select @@rowcount to see if any updates occurred. Then take the value from this and insert into a table. Make this the first step in your dts package.
On success, call another step to simply select the last value entered into that table into an output global variable. If it is 0 you can then fail the package accordingly.
The best way to do so is to use an activex script to check the value from the output variable and return a success or failure based on that result.
If DTSGlobalVariables("yourvariable").Value 0 then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
If you need any help assigning values to output global variables come back to this forum. They are quite easy. You want the ouput paramater type as row value.
You may be better off using xp_sendmail in the same sp if the rowcount of 0 is returned. It will save you a lot of work.
September 28, 2005 at 10:29 pm
>You cannot do a select @@rowcount into another ouput global variable >and use that value to see if any updates ocurred.
Let me disagree. The workaround is to use SET NOCOUNT ON as the first statement in the Execute SQL Task:
SET NOCOUNT ON
UPDATE MyTable SET MyValue = 5 WHERE MyFilter = 'Yes'
SELECT @@ROWCOUNT
Then click "Parameters", Select "Row Value", and set Output Global Variables to your integer global variable name.
I just ran a quick test to confirm that it works.
Anatol Romanov
September 29, 2005 at 3:41 am
Nice one, Anatol. I was overcomplicating it, trying to get a workaround.
I assumed that would stop the @@rowcount from working, but I have checked BOL and you are right.
"The @@ROWCOUNT function is updated even when SET NOCOUNT is ON"
September 29, 2005 at 8:56 am
Thank you gentlemen.. you were of great help!
October 1, 2005 at 4:37 am
You dont even need a global variable.
------------------------------------------------------------
Update TT set ff=xx Where ....
IF @@ROWCOUNT=0
RAISERROR 60001 'Error message'
------------------------------------------------------------
When you raise an error in your stored procedure, the sql task will receive the error condition and signals failure to the package which you can terminate using normal workflow. There is also a task property that automatically fails the package if it fails.
Habib.
October 3, 2005 at 7:34 am
I wouldn't recommend using RAISERROR as 100% reliable.
If an unexpected error occurs you will assume that the rowcount was zero when perhaps the table definition has changed. You want this error to be logged separately.
The activex script will check for the exact error of zero rowcount.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply