More than interesting...
-
Imagine this pseudo-code:
SP_WRITE_MESSAGE
INSERT INTO MSGSP_WRITE_COURSE
INSERT INTO COURSESP_WRITE_RESPONSE
IF(@SOME_COMPUTED_VALUE = 0)
BEGIN
RAISERROR('Some error message', 16, 1)
RETURN
ENDEXEC SP\_WRITE\_MESSAGE EXEC SP\_WRITE\_COURSENow there is an FK error in SP_WRITE_COURSE and I can see it (and fix) when running SP_WRITE_RESPONSE from SSMS, but running from the application (IIS hosted) with the same parameters I get 'Some error message', even I can see that SP_WRITE_MESSAGE executed... Anyone?
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
The return statement is never reached, because the raiserror exits the statement block. So you should either add an else statement or add try and catch blocks. And if you use SqlServer 2012 or newer you should also consider using throw instead of raiserror. You should also be careful with adding the sp_ prefix to your stored procedures, it doesn't mean "stored procedure". Is the sp_ prefix still a no-no? - SQLPerformance.com[^] This post should also be taken with a grain of salt, my brain is still pretty well baked.
Wrong is evil and must be defeated. - Jeff Ello
-
The return statement is never reached, because the raiserror exits the statement block. So you should either add an else statement or add try and catch blocks. And if you use SqlServer 2012 or newer you should also consider using throw instead of raiserror. You should also be careful with adding the sp_ prefix to your stored procedures, it doesn't mean "stored procedure". Is the sp_ prefix still a no-no? - SQLPerformance.com[^] This post should also be taken with a grain of salt, my brain is still pretty well baked.
Wrong is evil and must be defeated. - Jeff Ello
It was true, but the RAISERROR statement itself never reached as the if before it resolves to FALSE... Pay attention to the flow: 1. IF is FALSE 2. Does EXEC SP_WRITE_MESSAGE with success (can see the record in DB) 3. Does EXEC SP_WRITE_COURSE and fails. This is the point of interest... Running from SSMS I receive the expected FK violation error, running from ASP.NET (C#) I receive 'Some error message'... And I do not prefix my stored procedures SP_ in real life, just done it here to identify them as SPs... :-D
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
Imagine this pseudo-code:
SP_WRITE_MESSAGE
INSERT INTO MSGSP_WRITE_COURSE
INSERT INTO COURSESP_WRITE_RESPONSE
IF(@SOME_COMPUTED_VALUE = 0)
BEGIN
RAISERROR('Some error message', 16, 1)
RETURN
ENDEXEC SP\_WRITE\_MESSAGE EXEC SP\_WRITE\_COURSENow there is an FK error in SP_WRITE_COURSE and I can see it (and fix) when running SP_WRITE_RESPONSE from SSMS, but running from the application (IIS hosted) with the same parameters I get 'Some error message', even I can see that SP_WRITE_MESSAGE executed... Anyone?
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Kornfeld Eliyahu Peter wrote:
with the same parameters
One possibility of course is that you are not in fact using the same parameters. You didn't mention but presumably in both test cases you are using exactly same database instance (not a copy) and that you have verified that you are using the same instance.
-
Kornfeld Eliyahu Peter wrote:
with the same parameters
One possibility of course is that you are not in fact using the same parameters. You didn't mention but presumably in both test cases you are using exactly same database instance (not a copy) and that you have verified that you are using the same instance.
Same DB instance, same parameters, same error - only that I see the wrong error from code...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
It was true, but the RAISERROR statement itself never reached as the if before it resolves to FALSE... Pay attention to the flow: 1. IF is FALSE 2. Does EXEC SP_WRITE_MESSAGE with success (can see the record in DB) 3. Does EXEC SP_WRITE_COURSE and fails. This is the point of interest... Running from SSMS I receive the expected FK violation error, running from ASP.NET (C#) I receive 'Some error message'... And I do not prefix my stored procedures SP_ in real life, just done it here to identify them as SPs... :-D
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
Which means the IF behaves differently in the two environments. One common difference between SSMS and dotNet connection is the environment settings. The most interesting in this case is probably the ARITHABORT setting. It's normally ON in SSMS and OFF in a client connection. Try to set ARITHABORT ON or OFF in your code to check if there is a difference in behavoiur.
Wrong is evil and must be defeated. - Jeff Ello
-
The return statement is never reached, because the raiserror exits the statement block. So you should either add an else statement or add try and catch blocks. And if you use SqlServer 2012 or newer you should also consider using throw instead of raiserror. You should also be careful with adding the sp_ prefix to your stored procedures, it doesn't mean "stored procedure". Is the sp_ prefix still a no-no? - SQLPerformance.com[^] This post should also be taken with a grain of salt, my brain is still pretty well baked.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
The return statement is never reached, because the raiserror exits the statement block.
If the
RAISERRORstatement is executed, neither of theEXECcalls will be reached either. There's no need forELSEblocks. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Same DB instance, same parameters, same error - only that I see the wrong error from code...
Skipper: We'll fix it. Alex: Fix it? How you gonna fix this? Skipper: Grit, spit and a whole lotta duct tape.
-
Jörgen Andersson wrote:
The return statement is never reached, because the raiserror exits the statement block.
If the
RAISERRORstatement is executed, neither of theEXECcalls will be reached either. There's no need forELSEblocks. :)
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Don't you need errorlevel 20 or above to terminate batch?
Wrong is evil and must be defeated. - Jeff Ello
-
Don't you need errorlevel 20 or above to terminate batch?
Wrong is evil and must be defeated. - Jeff Ello
Looking at the documentation[^], a severity of 20 to 25 will not only terminate the batch; it will terminate the connection, and write an error to the error and application logs. It's not entirely clear, but as far as I can see, a severity of 11 to 19 will terminate the batch (or transfer to the
CATCHblock), but leave the connection intact.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Looking at the documentation[^], a severity of 20 to 25 will not only terminate the batch; it will terminate the connection, and write an error to the error and application logs. It's not entirely clear, but as far as I can see, a severity of 11 to 19 will terminate the batch (or transfer to the
CATCHblock), but leave the connection intact.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
It certainly is unclear. So I tested by creating a couple of procedures.
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
PRINT 'Start SubSP1'
RAISERROR('Some Error', 16,1) WITH LOG
PRINT 'End SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
PRINT 'Inside SubSP2'
ENDGO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
PRINT 'Start MainSP'
EXEC dbo.SubSP1
EXEC dbo.SubSP2
PRINT 'End MainSP'
END
GORunning the MainSP results in:
Start MainSP
Start SubSP1
Msg 50000, Level 16, State 1, Procedure SubSP1, Line 6
Some Error
End SubSP1
Inside SubSP2
End MainSPThis does not stop the execution at all, Neither on statement, scope, batch or connection level. Changing the errorlevel to 17 results in:
Start MainSP
Start SubSP1
End SubSP1
Inside SubSP2
End MainSP
Msg 50000, Level 17, State 1, Procedure SubSP1, Line 5
Some ErrorStill doesn't stop the execution, but note the difference in where the error message is printed. I suspect there will be some real differences between the errorlevels if I add TRY-CATCH or more probably a transaction. Errorlevel 19 or higher should stop the execution of the current batch according to Database Engine Error Severities | Microsoft Docs[^] but it doesn't. Errorlevel 20 and above closes connection as expected from the manual. Couldn't be bothered to check any further. I've proved myself wrong and thereby only proven that one shouldn't try to answer questions on New Years day.
Wrong is evil and must be defeated. - Jeff Ello
