Category: Error Handling

Vladimir Oselsky shows how to use THROW and RAISERROR for rethrowing exceptions:

Upon executing the first procedure, we get the error message back to the front end, but after checking balance, we find that money withdrawn from the account, but in the case of the second procedure, the same error returned to the front end but money still there.

Now we begin to scratch our head trying to figure out why we lost the money even though we got errors in both cases. The truth behind is the fact that RAISERROR does not stop the execution of code if it is outside of TRY CATCH block. To get same behavior out of RAISERROR, we would need to rewrite procedure to look something like following example.

There are some nuanced differences between THROW and RAISERROR, so it’s valuable to know how both work.

Monica Rathbun shows how to use the Steps Recorder in Windows:

Windows PSR “Problem Steps Recorder”.  It’s a nifty tool that helps you trouble shoot a computer problem by recording step by step what the user is doing.

How to:

• Go to Start

• Windows Accessories

• Steps Recorder or Problem Steps Recorder depending on Windows version

• Select Start Record (App will popup)

That’s a new one on me; click through to check it out.

Cody Konior tests how long messages in the error log can be:

Stupid question… what’s the schema of a table with sys.sp_readerrorlog output? Well you might be surprised if you’re used to using nvarchar(max) or nvarchar(2048).

There’s a datetime (modern datetime2(3)) obviously. ProcessInfo is either “Server” or “spidxxxy” where xxx is an int (max of 11 characters including minus) and y is an optional single character suffix. But as for the text…

Let’s try to max it out!

Moral of the story:  keep those error messages as short as possible while still being meaningful.

Dan Guzman goes into some detail about error handling in SQL Server:

T-SQL and ADO.NET data access code must work in concert with one another to ensure SQL errors are detected in application code. The T-SQL constructs used in multi-statement batches can affect if and how when errors are reported by ADO.NET during batch execution. I’ll start by citing core T-SQL error handling objectives, which can be summarized as:

1) Ensure a multi-statement T-SQL batch doesn’t continue after an error occurs.
2) Rollback transaction after errors.
3) Raise error so that the client application is aware a problem occurred.

Read the whole thing.

Dave Mason troubleshoots an annoying connectivity error message:

Ah! An “SSPI” error. I was certain I knew what the problem was. It had to be a failure to properly register the SPN. I was prepared to run a few SetSPN.exe commands to fix the problem and be on my way. To my surprise, the SPN *was* registered correctly, as I discovered in the Log File Viewer:

Click through for the solution.