can't trap error

Question

Post reply

can't trap error

peacesells

SSCrazy

Points: 2882
More actions
December 16, 2013 at 4:17 pm

#302459

SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(101)='SELECT 1/0'
DECLARE @r Table (R INT)
DECLARE @T Table (Z INT)
BEGIN TRANSACTION
INSERT INTO @r
EXECUTE sp_executesql @SQL
INSERT INTO @T
SELECT R FROM @r
IF @@Error <>0
BEGIN
PRINT 'Error'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT 'No Error'
COMMIT TRANSACTION
END
I can't seem to trap an error in my sql. It goes to else statement even when an error occurs, any suggestions? Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

If you're using 2012 (or 2005+) you should use TRY...CATCH

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(101)='SELECT 1/0'

DECLARE @r Table (R INT)

DECLARE @T Table (Z INT)

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO @r

EXECUTE sp_executesql @SQL

INSERT INTO @T

SELECT R FROM @r

PRINT 'No Error'

COMMIT TRANSACTION

END TRY

BEGIN CATCH

PRINT 'Error'

ROLLBACK TRANSACTION

END CATCH

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I'm sure that this is an example, but it wont hurt to remind you that table variables aren't affected by transactions. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

Since you are on 2012 you should use the TRY...CATCH blocks:

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(101)='SELECT 1/0'

DECLARE @r TABLE (R INT)

DECLARE @T TABLE (Z INT)

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO @r

EXECUTE sp_executesql @SQL

INSERT INTO @T

SELECT R FROM @r

PRINT 'No Error'

COMMIT TRANSACTION

END TRY

BEGIN CATCH

PRINT 'Error'

ROLLBACK TRANSACTION

END CATCH

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

The reason you are not getting the program flow you want is because this statement happens after the error but before you check for an error:

INSERT INTO @T

SELECT R FROM @r

This resets @@Error, which onlys report the result of the last statement.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Keith Tate (12/16/2013)
Since you are on 2012 you should use the TRY...CATCH blocks:

Mind reading? 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 6

Mine is just a little slower than yours. 😉

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

peacesells SSCrazy Points: 2882 More actions · Answer 7

mister.magoo (12/16/2013)
The reason you are not getting the program flow you want is because this statement happens after the error but before you check for an error:
INSERT INTO @T
SELECT R FROM @r
This resets @@Error, which onlys report the result of the last statement.

SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(101)='SELECT 1/0'

DECLARE @r Table (R INT)

DECLARE @T Table (Z INT)

DECLARE @ErrorCount INT

BEGIN TRANSACTION

INSERT INTO @r

EXECUTE sp_executesql @SQL

SET @ErrorCount=@@Error

INSERT INTO @T

SELECT R FROM @r

SET @ErrorCount=@ErrorCount+@@Error

IF @ErrorCount <>0

BEGIN

PRINT 'Error'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

PRINT 'No Error'

COMMIT TRANSACTION

END

How about this? Is this is a correct approach?

The reason i wanted to do this because i want the ssis package to fail when an error occurs ...try and catch would trap error but you don't see the package failed (therez work around ofcourse but not pretty). Thanks

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

That should catch the error happening, but it's not going to pass an error on to SSIS unless you specifically return an error code and handle that - wouldn't it be easier to just let the error happen?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

peacesells SSCrazy Points: 2882 More actions · Answer 9

mister.magoo (12/17/2013)
That should catch the error happening, but it's not going to pass an error on to SSIS unless you specifically return an error code and handle that - wouldn't it be easier to just let the error happen?

I have an update sql that runs right after where the error is likely to happen and I need to b able to roll back or avoid the update should an error occur.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 10

peacesells (12/17/2013)
mister.magoo (12/17/2013)
That should catch the error happening, but it's not going to pass an error on to SSIS unless you specifically return an error code and handle that - wouldn't it be easier to just let the error happen?
I have an update sql that runs right after where the error is likely to happen and I need to b able to roll back or avoid the update should an error occur.

You REALLY should read up on TRY . . . CATCH. Take a look at how I reworked your example code in the attached file. (Sorry I can't paste the code in the body of this post - my company's security systems block web page POST data that includes certain SQL keywords.)

Jason Wolfkill