March 6, 2014 at 5:09 am
Hi All,
I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.
I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:
BEGIN TRY
BEGIN TRANSACTION
<A whole bunch of inserts and updates>
COMMIT TRANSACTION
END TRY
BEGIN CATCH
<Error handling>
ROLLBACK TRANSACTION
END CATCH
BEGIN
<xp_cmdshell, BCP, FTP stuff>
END
What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?
Ta
David
March 6, 2014 at 5:13 am
dbalmf (3/6/2014)
Hi All,I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.
I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:
BEGIN TRY
BEGIN TRANSACTION
<A whole bunch of inserts and updates>
COMMIT TRANSACTION
END TRY
BEGIN CATCH
<Error handling>
ROLLBACK TRANSACTION
END CATCH
BEGIN
<xp_cmdshell, BCP, FTP stuff>
END
What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?
Ta
David
Why not move the xp_cmdshell to before the catch?
March 6, 2014 at 5:22 am
Hi,
I have had locking issues with the xp_cmdshell executing within the uncommited transaction so I have moved the catch up the script. I guess I could nest try/catch blocks which might work
D
March 6, 2014 at 7:32 am
Have you tried moving the <xp_cmdshell, BCP, FTP stuff> after the Commit Transaction?
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
March 6, 2014 at 7:46 am
This should work for you (create a variable and set the @@ERROR to it, then test for it before you attempt the xp_cmdshell)DECLARE @Err INT
BEGIN TRY
BEGIN TRANSACTION
<A whole bunch of inserts and updates>
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @Err = @@ERROR
<Error handling>
ROLLBACK TRANSACTION
END CATCH
IF @Err = 0
BEGIN
<xp_cmdshell, BCP, FTP stuff>
END
ELSE
BEGIN
'Do something else or GOTO whereever?'
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 5:06 pm
MyDoggieJessie (3/6/2014)
This should work for you (create a variable and set the @@ERROR to it, then test for it before you attempt the xp_cmdshell)DECLARE @Err INTBEGIN TRY
BEGIN TRANSACTION
<A whole bunch of inserts and updates>
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @Err = @@ERROR
<Error handling>
ROLLBACK TRANSACTION
END CATCH
IF @Err = 0
BEGIN
<xp_cmdshell, BCP, FTP stuff>
END
ELSE
BEGIN
'Do something else or GOTO whereever?'
END
Three points:
1) if you're going to test @Err for zero, you need to have set it to 0
2) don't rely on @@ERROR in a CATCH block, use ERROR_NUMBER() instead
3) If somehow ERROR_NUMBER() is 0 in the CATCH block, you still want to set @Err to something other than 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 6, 2014 at 6:39 pm
Absolutely, great catch Scott
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 7, 2014 at 2:37 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply