March 28, 2015 at 3:13 am
Hello Techies,
We are facing a serious conundrum in detecting an issue.
Here goes the details.
My application code(a diff framework similar to .net) is calling a SP1 in its process.
And in turn that SP1 calling another SP2 with in the cursor.
The flow is
Application code>>>>> SP1 >>>cursor begins>>calls another SP2>>cursor ends
SP2 code flow:
>> Begin Tran tr1
>>Delete statement on table 1 to avoid duplicates in the next insertion
>>Insert statement on table 1
>> If error <>0
rollback tr1
Return
>> Commit tr1
SP2 ends
the problem here is that while executing insert statement something is going wrong and causing transaction to rollback and control is returned from the SP. But the error is not thrown back to its calling SP (SP1 here)
This is causing a server impact to our business. say 100 thousand dollars.
Please note that there is no error handling mechanism in either SPs( except for if check in SP2) and in Cusror as well.
SP1 calls SP2 with in a cursor and irrespective of successful or failure of SP2 control is going to the next record
in the cursor and continuing with execution. Because of which we are unable to load transactions from few files.
Can you please explain for this odd behaviour. Note that the SP(Sp1 or SP2) never returned an error to application code as far as I know
Please help us with this.
Kind regards,
Uday
March 28, 2015 at 6:47 am
Quick thought, you may want to re-think the error handling in the stored procedure, consider this code
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.TBL_SAMPLE_RESULT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_RESULT;
CREATE TABLE dbo.TBL_SAMPLE_RESULT
(
SN_NUM INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_RESULT_SN_NUM PRIMARY KEY CLUSTERED
,SN_RES INT NOT NULL
);
IF OBJECT_ID(N'dbo.TBL_SAMPLE_NUMBERS') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_NUMBERS;
CREATE TABLE dbo.TBL_SAMPLE_NUMBERS
(
SN_NUM INT NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_NUMBERS_SN_NUM PRIMARY KEY CLUSTERED
);
INSERT INTO dbo.TBL_SAMPLE_NUMBERS (SN_NUM)
SELECT TOP(10)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
)
FROM sys.all_columns SAC;
GO
IF OBJECT_ID(N'dbo.USP_ERROR_ON_SIX') IS NOT NULL DROP PROCEDURE dbo.USP_ERROR_ON_SIX;
GO
CREATE PROCEDURE dbo.USP_ERROR_ON_SIX
(
@INPUT_NO INT
)
AS
SELECT
@INPUT_NO AS FROM_NUM
,@INPUT_NO / (@INPUT_NO % 6) AS TO_NUM
GO
IF OBJECT_ID(N'dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING') IS NOT NULL DROP PROCEDURE dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING;
GO
CREATE PROCEDURE dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING
AS
/* This procedure will NOT stop the execution on error */
DECLARE @IN_NUM INT = 0;
DECLARE R_SET CURSOR FAST_FORWARD
FOR
SELECT
TSN.SN_NUM
FROM dbo.TBL_SAMPLE_NUMBERS TSN
ORDER BY TSN.SN_NUM;
OPEN R_SET
FETCH NEXT FROM R_SET INTO @IN_NUM;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
INSERT INTO dbo.TBL_SAMPLE_RESULT (SN_NUM, SN_RES)
EXEC dbo.USP_ERROR_ON_SIX @IN_NUM
COMMIT TRAN
FETCH NEXT FROM R_SET INTO @IN_NUM;
END
CLOSE R_SET
DEALLOCATE R_SET
GO
IF OBJECT_ID(N'dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING') IS NOT NULL DROP PROCEDURE dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
GO
CREATE PROCEDURE dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING
AS
/* This procedure will stop the execution on error */
DECLARE @IN_NUM INT = 0;
DECLARE R_SET CURSOR FAST_FORWARD
FOR
SELECT
TSN.SN_NUM
FROM dbo.TBL_SAMPLE_NUMBERS TSN
ORDER BY TSN.SN_NUM;
OPEN R_SET
FETCH NEXT FROM R_SET INTO @IN_NUM;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO dbo.TBL_SAMPLE_RESULT (SN_NUM, SN_RES)
EXEC dbo.USP_ERROR_ON_SIX @IN_NUM
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
BREAK;
END CATCH
FETCH NEXT FROM R_SET INTO @IN_NUM;
END
CLOSE R_SET
DEALLOCATE R_SET
GO
/* Run the procedure without error handling
which will skip one row
*/
EXEC dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING;
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
TRUNCATE TABLE dbo.TBL_SAMPLE_RESULT;
GO
/*
This one will stop at the error
*/
EXEC dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
TRUNCATE TABLE dbo.TBL_SAMPLE_RESULT;
GO
/* This will fully rollback all inserts */
BEGIN TRY
BEGIN TRAN
EXEC dbo.USP_RUN_CURSOR_NO_ERROR_HANDLING;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
END CATCH
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
TRUNCATE TABLE dbo.TBL_SAMPLE_RESULT;
GO
/* This will also fully rollback all inserts */
BEGIN TRY
BEGIN TRAN
EXEC dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
END CATCH
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
No error handling results
SN_NUM SN_RES
----------- -----------
1 1
2 1
3 1
4 1
5 1
7 7
8 4
9 3
10 2
Results with error handling
SN_NUM SN_RES
----------- -----------
1 1
2 1
3 1
4 1
5 1
Results for outer (no error handling) procedure in a try catch
SN_NUM SN_RES
----------- -----------
Results for outer (with error handling) procedure in a try catch
SN_NUM SN_RES
----------- -----------
Edit: added missing code
March 28, 2015 at 8:01 am
Thank you!!
You mean to say that it never throws error back to application code(where try ,catch is used) if there is no proper error handling mechanism implemented in SQL.
But when i execute the outermost i.e SP1 which inturn executes SP2 i can see the error message in messages tab on sql server, though the SP1 is keep on getting executed after the error.
I never really get this why it is not throwing error to application code in my case (.net,java anythin)
March 29, 2015 at 1:39 am
Any help will be appreciated :-):-)
March 29, 2015 at 2:00 am
What the code example demonstrates is that even if the error message is passed up to the application, it wouldn't make much of a difference as the cursor will still execute the remaining iterations, there is nothing that can be done from the application side to control this. Therefore it is paramount that the errors are handled within the database procedural code. After the error has been dealt with, another error message can be thrown to notify the application.
😎
BEGIN TRY
BEGIN TRAN
EXEC dbo.USP_RUN_CURSOR_WITH_ERROR_HANDLING;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
THROW 51000, 'DIVISION BY ZERO!', 1;
END CATCH
GO
SELECT
TSR.SN_NUM
,TSR.SN_RES
FROM dbo.TBL_SAMPLE_RESULT TSR;
GO
Error message
Msg 51000, Level 16, State 1, Line 154
DIVISION BY ZERO!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply