C# cmd.ExecuteScalar(): "Cannot continue the execution because the session is in the kill state."

Question

Getting a weird exception from ExecuteScalar() that I cannot find any help for on the web:

Cannot continue the execution because the session is in the kill state.

I'm using SqlConnection/SqlCommand

The command is a basic INSERT INTO... with 105 columns (and 105 parameters to set the column data) followed by a SELECT SCOPE_IDENTITY();

I've checked the connection string - it is correct and the connection is open.

I'm not even sure what this error is telling me to know where to start looking on this one.

So what exactly does this error mean? How does a session get in the kill state to begin with?

Code is pretty straight forward:

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand(@"INSERT INTO VendorNote (VendorId, AdminComment...) VALUES (@VendorId, @AdminComment, ...); SELECT SCOPE_IDENTITY(); ", conn))
    {
        cmd.Parameters.AddWithValue("@VendorId", VendorId);
        cmd.Parameters.AddWithValue("@AdminComment", AdminComment);
        Id = (int) cmd.ExecuteScalar();
    }
}

Are you creating the connection just for this command, or trying to share the connection between multiple operations? — Jon Skeet, Dec 29 '15 at 20:04
It's not a problem with connection string or query syntax. Even if I replace my query with a simple select statement I get this error. Also - using this connection string all over the app successfully. So somehow the session must be getting corrupted or something. I'm back tracing the code that is executed before this right now to see if I can't narrow it down. — William Madonna Jr., Dec 29 '15 at 21:45

score 7 · Answer 1 · answered Dec 29 '15 at 22:13

FOUND IT!

There was a constraint violation in the query that was causing execution of the query to fail. Instead of reporting that info in the exception - it was reporting that the session was in a "kill state" (I'm guessing ) because the query was terminated prematurely.

I've never seen this error before - normally constraint errors and such have something more useful in the exception.

So anybody getting this error - REALLY check over your query to make sure it's valid.

score 2 · Answer 2 · answered Dec 29 '15 at 20:13

2

Your code should look like this:

const string sqlString = "INSERT INTO dbo.Table ( ....) " +
                         "               VALUES ( .... );" +
                         "SELECT SCOPE_IDENTITY();";
using (conn)
{
    using (var cmd = new SqlCommand(sqlString, conn))
    {
        cmd.Parameters.AddWithValue("@param", param);

        cmd.CommandType = CommandType.Text;
        conn.Open();
        return (int) (decimal) cmd.ExecuteScalar();

    }
}

But note sometime a stored procedure will be more appropriate

answered Dec 29 '15 at 20:13

meda

45,103
14
92
122

1

And why is that change necessary? – Josué Zatarain Nov 12 '18 at 19:06
its been a while my friend, I must have done this to make the connection disposable – meda Nov 12 '18 at 20:23

score 2 · Answer 3 · answered May 09 '18 at 16:47

It could be due to an Always On Availability Group 'Failover'... Whenever I have a running query, connected to an AG Listener, and there is a failover, I get an SqlException having the Message of 'Cannot continue the execution because the session is in the kill state.'...

C# cmd.ExecuteScalar(): "Cannot continue the execution because the session is in the kill state."

3 Answers3

Linked