Why is my SqlCommand returning a string when it should be an int?

Question

I have a query that should always be returning a single int. I have now logged it returning a string entirely unrelated to what it should be.

We've been getting some random FormatExceptions that we've tracked down to several database queries. After some additional logging, I found that, this morning, the query below returned the string "gladiator". Website.PkID is an int column and works most of the time, but some times it fails miserably and returns either an int that's waaaay out there (bigger than any valid WebsiteID) or a random string.

This particular query is hit once per session start. It's not using a shared connection, so I'm having trouble understanding how it could get such a mixed-up result. Could there be some kind of corruption in the connection pools?

I don't think the problem is isolated to this query. I've seen similar FormatExceptions (because of an unexpected result) coming from LINQ queries as well. We've also spotted some of these errors around the same times:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.

Could it be a connection issue? Or maybe we're getting result sets mixed up between the db server and the web server? This has really got me scratching my head.

Offending query:

public static int GetActiveWebSiteID(string storeID, string statusID)
{
    int retval;

    string sql = @"SELECT isnull(MAX(PkID),0) FROM WebSite 
                   WHERE StoreID = @StoreID 
                   AND WebSiteStatusID = @WebSiteStatusID";

    SqlConnection conn = new SqlConnection(Settings.ConnString);
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@StoreID", (object)storeID ?? DBNull.Value);
    cmd.Parameters.AddWithValue("@WebSiteStatusID", (object)statusID ?? DBNull.Value);

    conn.Open();
    using(conn)
    {
        var scalar = cmd.ExecuteScalar(); // <-- This value returned here should only ever be an int, but randomly is a string

        retval = Convert.ToInt32(scalar);
    }
    return retval;
}

The above query has worked fine for years until recently. We now have a bunch of additional LINQ queries in the app (not sure if that makes a difference). We're running .Net 3.5.

Answer 1

After a few months of ignoring this issue, it started to reach a critical mass as traffic gradually increased. We increased logging and found numerous definitive instances where, under heavy load, completely different result sets would get returned to unrelated queries.

We watched the queries in Profiler and were able to see that the bad results were always associated with the same spid, and that each bad result was always one query behind the actual sql statement being queried. It was like a result set got missed and whatever result set was next in the spid (from another connection in the same pool) was returned. Crazy.

Through trial and error, we eventually tracked down a handful of SqlCommand or LINQ queries whose SqlConnection wasn't closed immediately after use. Instead, through some sloppy programming originating from a misunderstanding of LINQ connections, the DataContext objects were disposed (and connections closed) only at the end of a request rather than immediately.

Once we refactored these methods to immediately close the connection with a C# "using" block (freeing up that pool for the next request), we received no more errors. While we still don't know the underlying reason that a connection pool would get so mixed up, we were able to cease all errors of this type. This problem was resolved in conjunction with another similar error I posted, found here: What Causes "Internal Connection Fatal Errors"?