CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008
DFord
DFord

Reputation: 2358

I get this SQL A transport-level error, but not all the time

I try to query data from the database and sometimes I get this error in my log:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The handle is invalid.) The code is inside a try-catch block.

What is weird is that I do not get this error all the time. The catch block does get executed.

However, whether I get the error or not, the Drop Down Lists I am trying to populate do not get populated. It seems like there is no data being returned from the query.

I am making a connection to the database, the data is in there, I can open a connection and run queries to the database. The database is on the same machine as where I'm running the code from.

Also, the code works when I connect to the database on the server, but I am trying to make a local database to work when I don't have connection to the server. I just have to make a change to the connection string when I want to run from the local database.

Here is the function that queries the database:

public static void QueryDB(string query)
    {
        cmd = new SqlCommand();
        cmd.CommandText = query;
        cmd.Connection = MyConn;
        if (dr != null) dr.Dispose();
        dr = cmd.ExecuteReader();
    }

Here is the connection string to connect to the database:

MyConn = new SqlConnection("Data Source=tcp:localhost; Database=EscalationManagementSystem; Integrated Security=true;");

Is there a setting that is wrong or could this be a bug in the code?

Thanks in advance.

Upvotes: 2

Views: 30022

Answers (4)

Uttam
Uttam

Reputation: 736

In my case, the query I was executing was reading data from an Excel file using ACE OLE DB drive (Microsoft.Ace.OLEDB.12.0). The driver had got corrupted and reinstalling the same fixed the issue.

The script was being executed from PowerShell and it was crashing SQL Server instance also and after that the SQL Server instance will not restart. It was hard to find this cause.

The error message received was:

Invoke-Sqlcmd : A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) Msg 109, Level 20, State 0, Procedure , Line 0.

I was going all around changing login, checking folder permissions, error log, database corruption etc. but in my case this was the cause. May help someone.

Upvotes: 0

mahantesh b w
mahantesh b w

Reputation: 11

This problem is due to the fact that you have not released your database connection. Just add a Finally statement in your code and add conn.Close() and conn.Dispose(). I am sure it will work fine.

finally 
{
    cmd.Connection.Close();
    cmd.Connection.Dispose();
}

Upvotes: 1

Dinesh
Dinesh

Reputation: 3770

Such an error usually appears when your connection to the instance of the Sql server is reset.

The cause maybe due to reset of the network connection.

Other cause might be:

You request the SQL Server and receive the data from the Connection pool. Now something bad happens to the SQL Server (service restart) and you make a postback to get data.

Check to make sure that your connection does not break between subsequent requests.

Hope this helps.

Upvotes: 3

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

I think this is a bug in your code. I don't think you should have a single SqlDataReader and SqlCommand that is shared by all calls to the database.

Instead, you should instantiate a new SqlDataReader and SqlCommand for each call to the database, and dispose (and close) them when the application is finished with that specific SQL call.

My guess as to what is happening in your situation would be that the SqlDataReader or SqlCommand is being pulled out from under you by another call to the database, and that the issue is intermittent because it's a race condition.

Upvotes: 3

Related Questions