How to simulate error with MSG = 0 on Sql Server?

Question

I need to simulate error on Sql Server:

“Msg 0, Level 20 A severe error occurred on the current command”

because our developers have to handle it in application.

How can I simulate/trigger this kind of error ? (I can also try on my localhost).

Answer 1

This error is returned by the client API, not SQL Server, when something bad happens like an access violation or killed connection that closes the connection on the server side. That's why the message id is zero instead of one in sys.messages. `Erland Sommarskog calls this a false error message in his article.

RAISERROR with a severity of 20 (which requires WITH LOG and sysadmin or alter trace permissions) may be able to simulate the error depending on the client API you are using. I see the error in SSMS (which uses the .NET framwork SqlClient API) after running the command below, along with other error messages. Note that a SQL exception may generate multiple error messages, including this one, so you will need to examine the errors returned for the one(s) of interest.

RAISERROR('This is a severe error test',20,0) WITH LOG;

Answer 2

You should be able to use RAISERROR or THROW. There is an existing post here that talks about the differences of each. However, you cannot generate a Msg 0 with these, so I'm not sure you'll be able to get the exact result you're looking for from either. Below is more documentation on each command.

RAISERROR

MS Docs - RAISERROR

RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]  

THROW

MS Docs - THROW

THROW [ { error_number | @local_variable },  
        { message | @local_variable },  
        { state | @local_variable } ]  
[ ; ]  

However, instead of focusing on the exact error message, I'd have your dev team make sure they are properly accepting all SQL errors in general. If they're able to properly capture SQL errors that are returned, then it should be easy for them to determine how to handle each unique error, such as this one. In that case, just generate any SQL error you want and have them show you they capture the message as you would have expected.

The specific error in the question is caused only by an exception on the server—a product bug—so will be tough to simulate exactly.