Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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();
    }
}
like image 843
William Madonna Jr. Avatar asked Dec 24 '22 11:12

William Madonna Jr.


2 Answers

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.

like image 58
William Madonna Jr. Avatar answered Dec 27 '22 04:12

William Madonna Jr.


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

like image 37
meda Avatar answered Dec 27 '22 04:12

meda