December 16, 2013 at 4:17 pm
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.
December 16, 2013 at 4:30 pm
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
December 16, 2013 at 4:32 pm
I'm sure that this is an example, but it wont hurt to remind you that table variables aren't affected by transactions. 😉
December 16, 2013 at 4:32 pm
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
December 16, 2013 at 4:34 pm
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);
December 16, 2013 at 4:45 pm
Keith Tate (12/16/2013)
Since you are on 2012 you should use the TRY...CATCH blocks:
Mind reading? 😀
December 16, 2013 at 4:51 pm
December 16, 2013 at 5:07 pm
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
December 17, 2013 at 5:08 pm
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);
December 17, 2013 at 10:03 pm
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.
December 19, 2013 at 9:35 am
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply