C# cmd.ExecuteScalar(): “Cannot continue the execu

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();
    }
}

Answer 1

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.'...

Answer 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

Answer 3

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.