I thought it might be useful to start a semi-regular series of entries on troubleshooting for the SQL Server developer. Google has come to my rescue lo, these many times: this is my way of returning the favor. For this first entry I thought I’d tackle one of the more mysterious-looking runtime errors I see from time to time.
It usually goes about like this – code that has been running fine for ages, or which works fine for everyone else in the universe, suddenly starts bailing out with a runtime error. The error, when located, looks like this:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
It’s really a frightful-looking message, and worse, double-clicking on the message in SSMS does not jump your cursor to the source of the error. Well great.
This is where your mileage may vary, but for me, I generally see this error crop up in code which follows this pattern:
- The batch is utilizing an extended stored procedure
- The output of the extended stored procedure is being insert into a table
By way of example, here’s a snippet of code that will reliably cause the error:
create table #ErrorLog (LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(500)) insert into #ErrorLog exec xp_readerrorlog 'authentication mode' drop table #ErrorLog
In this case the output of xp_readerrorlog is being insert into a temporary table. However, if you’re familiar with xp_readerrorlog you’ll recognize that there’s something not quite right going on here. If you take the outer insert out of the equation and run the procedure by itsef, the error message becomes clearer:
exec xp_readerrorlog 'authentication mode'
The output in this case is:
Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type
Yes, indeed, ‘authentication mode’ is not a valid parameter value here: the procedure is expecting an integer. That’s much clearer than “a severe error.” Fixing that bug solves the issue.
create table #ErrorLog (LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(500)) insert into #ErrorLog exec xp_readerrorlog 1,1,'authentication mode' drop table #ErrorLog
This is of course a contrived example and this code would never have worked, but often in production these errors are more subtle. I have seen the inner exception be an overflow, a permissions problem, or even outright corruption, and often it’s not as clear-cut and perfectly reproducible as this example. For instance, the stored procedure in the outer batch may be a regular system or user procedure which in turn calls an extended stored procedure, but only with certain codepaths. You may really need to peel the onion on this one.
Certainly I don’t believe this is the only error case that can lead to this particular message, but for what it’s worth, in my experience looking for the XSP has generally put me on the right path toward quashing the bug. If you have any other experience with this error, please let me know either through my contact page or through the comments.