How to simulate error with MSG = 0 on Sql Server?
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).
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 https://www.sommarskog.se/error_handling/Part2.html#falseerrmsg .
RAISERRORwith a severity of 20 (which requires
WITH LOGand 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;