Troubleshooting “A severe error occurred on the current command.”

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.

Happy debugging!

March 21, 2012 Posted by: 6 Comments - Permalink
6 Responses to Troubleshooting “A severe error occurred on the current command.”
  1. Martin Ingham says:

    Thanks Vicky, your post put me on the right track. In my case the problem was caused by a problem in a stored procedure (not an extended SP) whose output I was inserting into a table variable.

  2. JSP says:

    Very nice article..

  3. hafiz says:

    hi.. i have problem with this issues. I try to execute your script :
    create table #ErrorLog (LogDate datetime, ProcessInfo nvarchar(50), LogText nvarchar(500))
    insert into #ErrorLog exec xp_readerrorlog 1,1,’authentication mode’
    drop table #ErrorLog

    But still nothing happen. Need help. Thanks

  4. hafiz says:

    It’s working now. Thanks. :)

  5. aweinca says:

    My issue with error is ‘skipping’ over it. Nothing I have tried works including TRY Catch.
    Everytime my script encounters this error it stops dead. I need it to continue. I’m wondering if this error is so sever that it cannot be skipped over. Any help would be greatly appreciated. Thanks in advance

  6. Ray says:

    Hi Vicki,
    My example is Msg 0 Level 11.
    I have a “nibbler” script that shrinks a DB file by a small increment. The script loops until a set time or it reaches the specified target size. I have to do this to make Prod backups fit in Dev and Staging :(.

    Anyway the only unusual command is the DBCC Shrinkfile which is executed as dynamic SQL. The relevant code snippet is:

    Begin Try
    Exec sp_ExecuteSQL @cmd;
    Set @duration = datediff(ms, @StartTime, GETDATE());
    Set @cntConsecutiveDeadLock = 0; —
    RaisError(‘Completed %dMB shrink in %d ms. Count = %d’, 0, 0, @ShrinkIncrementMB, @duration, @cnt) With NoWait;
    WaitFor Delay @Delay;
    End Try — Do the Shrink

    The error occurs randomly (or at least unpredictably) but often enough that it is bothersome. Typically restarting the script runs fine.

Leave a Reply

%d bloggers like this: