June 21, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/doesyourspchockonerrors.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
June 26, 2003 at 12:33 am
Hi Robert, good article, ive used this sort of technique with large data migration efforts and works well. I cant only dream for managed exception handling for t-sql in yukon 🙂
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 26, 2003 at 1:29 am
Nice article. On a related note do you have any ideas on how to detect whether a stored procedure completed execution when run from ADO?
For instance take the following SQL;
CREATE TABLE TestTable (
x INT NOT NULL
CONSTRAINT CK_TestTable_x CHECK(x = 1)
)
GO
CREATE PROCEDURE TestSP
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO TestTable (x) VALUES (2)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
INSERT INTO TestTable (x) VALUES (1)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -2
END
COMMIT TRANSACTION
RETURN 0
END
GO
and the following VB program;
Dim conn As Connection
Dim cmd As Command
Dim prm As Parameter
Set conn = New Connection
Set cmd = New Command
Call conn.Open("File Name=c:\localhost.udl")
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "TestSP"
Set cmd.ActiveConnection = conn
Set prm = cmd.CreateParameter("", adInteger, adParamReturnValue)
Call cmd.Parameters.Append(prm)
cmd.Execute
Call MsgBox(prm.Value)
conn.Close
Set conn = Nothing
Set cmd = Nothing
ADO raises an error for the constraint violation, but actually the stored procedure handles it. To further confuse an error is not raised if the insert statements are swapped so it is the second that fails instead of the first.
Any ideas?
June 26, 2003 at 2:16 am
If your store procedure fails before hitting a RETURN statement then the return value will be zero. This is the same value that you have used if your stored proc suceeds therefore your stored procedure needs to return a non-zero value to indicate success.
June 26, 2003 at 2:25 am
Robert,
I would generally consider selecting from a non-existant table to be a bug rather than an error.
In cases where it can be a legitemate programming error I would tend to use
IF NOT EXISTS(SELECT 1 FROM SysObjects WHERE Type='U' AND Name = 'MyTable'
BEGIN
RAISERROR('MyTable is missing',11,1)
RETURN 0
END
June 26, 2003 at 6:30 am
I ran the following in Query Analyzer:
CREATE PROCEDURE spTest1
AS
SELECT * FROM NoTable
RETURN 1
GO
DECLARE @v-2 int
exec @v-2 = spTest1
PRINT @v-2
And I got the following error:
Server: Msg 208, Level 16, State 1, Procedure spTest1, Line 4
Invalid object name 'NoTable'.
From this I would say that if the execution of a stored procedure abruptly and completely halts then you will not get a return code, unless of course things happen differently with ADO.
I'll say right now I know almost nothing about ADO. I also know very little VB. The closest I get to that is VB scripts in DTS packages. From my knowledge of SQL, it looks to me as if you should be able to get the return code from the SP as long as it doesn't halt as in the example above.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
June 26, 2003 at 6:35 am
As for the non existant table, that was simply the first SP I came across that would stop on encountering the error and so I used it to illustrate a point in my article.
I am glad you all liked the article. I am trying to provide examples (if nothing else) that I am not able to find in BOL to better illustrate how SQL Server handles stored procedures.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
June 26, 2003 at 7:16 am
On the ADO error thing I tend to have a local stored procedure variable that I use to record errors that occur within the stored procedure.
Obviously I try and test and trap the situation that may fail because letting it occur may result in a stopped procedure.
I'll either have this as a straight "count the errors" type thing or as a bit map value so I can say "this error happened" AND "this error happened".
I will either have this as the @@RETURN_VALUE parameter (the one passed back to VB by the SQL RETURN statement) or as a.n.other SQL OUTPUT variable.
If there is some complicated logic within the stored procedures that would cause problems in flow then I try and break out that functionality into separate procedures.
I've done some experimenting with the severity levels to see what fires off the VB error trapping routines. I don't have my results to hand but I think errors with severity 11 and above raise errors in the VB program that can be caught with the ON ERROR GOTO statement. I seem to remember that you get a generic ODBC error number + whatever message is actually returned from SQL.
June 26, 2003 at 7:18 am
I haven't run the procs as described in the article but the most troubling error handling I've had to deal with are errors like PK violations that abrubtly halt the entire batch that was sent by the client, in this case no matter how far you've nested the procs, the entire process is aborted and an error returned to the client, I would like to figure out how to trap these kinds of errors in TSQL and deal with them before my procs return control. For other ways of dealing with errors, see the master database and the SQL Server's own sp_ procs.
June 26, 2003 at 7:30 am
quote:
I've had to deal with are errors like PK violations ... I would like to figure out how to trap these kinds of errors in TSQL and deal with them before my procs return control. For other ways of dealing with errors, see the master database and the SQL Server's own sp_ procs.
To test for primary key violations I just use syntax like
IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyField = @Value)
BEGIN
INSERT MyTable(My Field) VALUES (@Value)
RETURN 1
END
ELSE
BEGIN
RAISERROR('My bespoke PK Error',10,1)
RETURN 0
END
I raise bespoke errors simply because I want to raise them with a lower severity level than would cause an stoppage.
Generally, most things can be trapped but the more error trapping is done at the SQL end the bigger the performance hit.
If there are going to be multiple apps hitting a database then I put more error trapping in the database and middle tier. If there are very few apps then I expect the app to take the burden of the error trapping.
June 26, 2003 at 7:44 am
quote:
Generally, most things can be trapped but the more error trapping is done at the SQL end the bigger the performance hit.If there are going to be multiple apps hitting a database then I put more error trapping in the database and middle tier. If there are very few apps then I expect the app to take the burden of the error trapping.
Very true! But in a high transaction environment this is not always doable. I guess I've got a general gripe at the way SQL Server/T-SQL handles errors, because for the errors we've been talking about (and without using the trapping you've described), as stored procedure developers, we have to say 'we can trap some errors but not all..' given that, it splits the error trapping duties between the stored proc and the client/middle tier, when what we need at the stored proc level is a comprehensive trapping mechanism. The problem is even more complicated when you add in transaction handling as it's not always clear what was rolled back but that's another thread...
June 26, 2003 at 8:19 am
The problem I have is that Key violations do not terminate the current batch (unless XACT_ABORT IS set) But ADO does put a client application in the error handler.
My problem with ADO is that I believe that it should only raise an error if the command execution was terminated. SP programmers would have to implement an error reporting using output variables similar to that specified by David above.
June 26, 2003 at 8:28 am
I don't think sp spStaticNoStopOnFirstError can continue if the first query run into an error.
June 26, 2003 at 8:34 am
I believe this code:
IF NOT EXISTS (SELECT 1 FROM MyTable WHERE MyField = @Value)
BEGIN
INSERT MyTable(My Field) VALUES (@Value)
RETURN 1
END
ELSE
BEGIN
RAISERROR('My bespoke PK Error',10,1)
RETURN 0
END
is still flawed (for The Error trapping purpose that was intended to)
Let's suppose we are in a highly transactional environment and two procedures are executing the same code. On the "Exists" clause both get the "false" but then one of them will insert the PK that maybe the other will try too! Then on the second procedure the insert will raise the "avoided" error to the client. You could have placed a lock but that would be even worse
I would rather rewrite it as:
INSERT MyTable(My Field) VALUES Where MyField <> @Value
IF @@ROWCOUNT = 0
RAISERROR('My bespoke PK Error',10,1)
But in any case, the solution will really be:
Microsoft, Please
STRUCTURED EXCEPTION HALNDLING!!!!!!
* Noel
June 26, 2003 at 8:37 am
spStaticNoStopOnFirstError will not continue as the table it refers to does not exist.
However an sp will continue after a unique/primary key violation.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply