Error Handling – Darling Data

The Empty Catch Block: Handling Errors In SQL Server… Weirdly.

Posted on May 2, 2022August 5, 2022 by Erik Darling

Don’t Know Why

Normally people handle errors to… handle errors. But I came across someone doing something sort of interesting recently.

Before we talk about that, let’s talk about the more normal way of capturing errors from T-SQL:

CREATE OR ALTER PROCEDURE
    dbo.error_muffler
(
    @i int
)
AS 
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
    
        SELECT 
            x = 1/@i;
    
    END TRY
    
    BEGIN CATCH
        /*Do some logging or something?*/
        THROW;
    END CATCH;

END;

So if we execute our procedure like this, it’ll throw a divide by zero error:

EXEC dbo.error_muffler 
    @i = 0;

Msg 8134, Level 16, State 1, Procedure dbo.error_muffler, Line 12 [Batch Start Line 33]

Divide by zero error encountered.

Well, good. That’s reasonable.

Empty Iterator

What I recently saw someone doing was using an empty catch block to suppress errors:

CREATE OR ALTER PROCEDURE
    dbo.error_muffler
(
    @i int
)
AS 
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    BEGIN TRY
    
        SELECT 
            x = 1/@i;
    
    END TRY
    
    BEGIN CATCH
        /*Nothing here now*/
    END CATCH;

END;
GO

So if you execute the above proc, all it returns is an empty result with no error message.

Kinda weird.

Like not having finger or toenails.

Trigger Happy

Of course (of course!) this doesn’t work for triggers by default, because XACT_ABORT is on by default..

CREATE TABLE 
    dbo.catch_errors
(
    id int NOT NULL
);
GO

CREATE OR ALTER TRIGGER
    dbo.bury_errors
ON
    dbo.catch_errors
AFTER INSERT
AS
BEGIN
    BEGIN TRY
        UPDATE c
            SET c.id = NULL
        FROM dbo.catch_errors AS c;
    END TRY
    BEGIN CATCH

    END CATCH;
END;
GO

If we try to insert a row here, we’ll get a really weird error message, unswallowed.

INSERT 
    dbo.catch_errors
(
    id
)
VALUES
(
    1
);

Womp:

Msg 3616, Level 16, State 1, Line 29

An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

If we were to SET XACT_ABORT OFF; in the trigger definition, it would work as expected.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Trying Out Azure Data Studio Query Plans

Posted on March 30, 2022May 16, 2022 by Erik Darling

Do You Really Wanna Hurt Me?

Hi, I’m Erik. You may remember me from such hits as Make ADS An Optional Install Alongside SSMS.

While I still don’t install Azure Data Studio by default, I heard recently from Erin Stellato (t) that there’s a preview of real, live, actual execution plans out now.

Since not having query plans available was one of my chief gripes about ADS, I figured this was a good time to install it.

The pro here is that it looks like the query plan assets are much higher DPI than the ones in SSMS, but… I still can’t picture using it every day just yet.

There’s some key stuff missing, like being able to hide query results, and plan operators don’t show parallelism yet. While I hear that a lot of improvements are on the to-do list, between whatever is going on internally along with the GitHub backlog, I have no idea what the timeline is for them.

A Query Plan In Azure Data Studio

If you enable the workbench editor enable preview setting, you can hit CTRL+M to get an actual execution plan. Here’s what it looks like:

The arrow is just to point out the the plan does have parallelism engaged, but it is not shown anywhere (yet, I know, I know).

It is cool that you can get the full properties of any of the operators, and that operator times are consistent with what SSMS shows. Also, I like the highly accusatory, Snag-It style arrows here.

The Same Plan In SSMS

I recently changed the font that my query plans use to one that’s a bit less offensive. The only point here is that in this plan you can see parallelism more easily, and maybe that the operator icons aren’t quite as spiffy.

Aside from slight variations in CPU timing, the plans are virtually identical. Good!

Kind of.

Six Different Ways

My criticisms of how query plans are displayed are somewhat nit-picky, and I’d even wager that some people might disagree with me on how things should show up.

For example, i think it’s quite confusing that batch mode operators track time differently than row mode operators, and that there’s no real visual indication as to which operators use batch mode. I also very much dislike that in both the ADS and SSMS plans, the index operation is cut off. “Clustered Index S…” doesn’t help anyone.

I’d also find it useful to have an I/O rollup in the root node, where other Query Time Statistics are. But hey.

When you get deeper into things, stuff like a visual indicator of how many times an operator executed, and missing index requests for Eager Index Spools would be nice, too.

Anyway, I’m still not the target audience for ADS (at least so far as I can tell), and I’ll be sticking with SSMS for now, but this is a very nice addition for folks who ADS is more useful for.

Thanks for reading!

Going Further

Raising Errors And Eyebrows With RAISERROR In SQL Server

Posted on June 12, 2020May 14, 2022 by Erik Darling

Part One

I had a client get in touch recently because they thought they had corruption. That’s not normally my cup of tea, but I offered to jump on and take a look.

We got to talking, and they said users had been getting some nasty error messages. Stuff like this:

Msg 2745, Level 16, State 2, Procedure dbo.oh_no, Line 8 [Batch Start Line 13]
Process ID 56 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Procedure dbo.oh_no, Line 8 [Batch Start Line 13]
SCARY THINGS
Msg 596, Level 21, State 1, Line 13
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 13
A severe error occurred on the current command.  The results, if any, should be discarded

I don’t know if you’ve ever seen corruption error messages, but they’re… uh. Look, there’s a reason I send people to Microsoft. It’s $500 well spent.

Thankfully this was just an overzealous new developer who wanted to show people the wonders and miracles of error handling.

If you use RAISERROR with a severity level of 20 or higher, and the WITH LOG clause, it kills the connection. Without the WITH LOG CLAUSE, you won’t get a proper error, and it also wouldn’t enter a CATCH block.

CREATE OR ALTER PROCEDURE dbo.oh_no
AS
SET NOCOUNT, XACT_ABORT ON; 
BEGIN

    IF 1 < 2
        BEGIN
            RAISERROR('SCARY THINGS', 20, 1) WITH LOG, NOWAIT;
        END;

END;
GO 

EXEC dbo.oh_no;
GO

What you return to people can be mighty confusing. Especially if your application only shows the first error message. Ahem.

I’d probably be worried if I started seeing that pop up, too.

Part Two

Only somewhat related to part one, but probably a good time to mention it: If you want RAISERROR to get you to a TRY/CATCH block and throw an error, you have to use a severity 16 or higher and WITH LOG. For instance, this won’t work:

CREATE OR ALTER PROCEDURE dbo.oh_yes
AS
SET NOCOUNT, XACT_ABORT ON; 
BEGIN

    IF 1 < 2
        BEGIN TRY
            
            RAISERROR('IS THIS THING ON?', 10, 1) WITH LOG, NOWAIT;
        
            SELECT 'NO SHUT UP' AS NO_U;

        END TRY
        BEGIN CATCH
            THROW;
        END CATCH;
END;

EXEC dbo.oh_yes;

Your code will keep right on chugging along.

Despite the fact that an “error” was raised.

Are you German?

You need to give this old hound a higher severity (and WITH LOG) to get it to bark.

RAISERROR('IS THIS THING ON?', 16, 1) WITH LOG, NOWAIT;

Then you get it back in red.

SQL Server Error Message — I used to be like you.

Parting

Error handling is good for all sorts of things. You can retry deadlocks, handle and log edge cases, keep loops alive, and more.

RAISERROR is useful for figuring out where you’re at in some logic — and I know, it’s deprecated — but I find using the combination of THROW and FORMATMESSAGE tedious and cumbersome. I don’t want to add things to sys.messages. I don’t want to use two functions instead of one to pretty print some information. What’s the point?

Thanks for reading!

Going Further

Error Handling In SQL Server Cursors

Posted on May 5, 2020May 16, 2022 by Erik Darling

Spit My Last Error

Going Further

If you wanna play with the code I used to try things out on your own, here it is:

CREATE TABLE zero_ten (id INT NOT NULL PRIMARY KEY);

INSERT dbo.zero_ten ( id )
SELECT 0 AS id
UNION ALL 
SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0)
FROM sys.messages AS m

SELECT * FROM dbo.zero_ten AS zt;
RETURN;

DECLARE c CURSOR LOCAL STATIC FOR
SELECT N'DECLARE @atmosphere INT; SELECT @atmosphere = 1/' + RTRIM(zt.id)
FROM dbo.zero_ten AS zt;

DECLARE @joydivision NVARCHAR(MAX) = N'';
DECLARE @errors TABLE
(
    id INT PRIMARY KEY IDENTITY,
    error_number INT,
    error_severity INT,
    error_state INT,
    error_message NVARCHAR(MAX)
);

OPEN c;
FETCH NEXT FROM c
INTO @joydivision;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        RAISERROR(@joydivision, 0, 1) WITH NOWAIT;
        EXEC sys.sp_executesql @joydivision;
        FETCH NEXT FROM c INTO @joydivision;
    END TRY
    BEGIN CATCH
        INSERT @errors ( error_number, error_severity, error_state, error_message )
        SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE();
        --FETCH NEXT FROM c INTO @joydivision;
    END CATCH;
END;

SELECT *
FROM @errors AS e;

CLOSE c;
DEALLOCATE c;
GO