Category Archives: Databases

Solution for: A transport-level error has occurred when receiving results from the server…

We have a program written in C# using Visual Studio 2012 and .NET 4.0 / .NET 4.5. It rapidly executes many queries in Microsoft SQL Server 2008 R2 and then exits. Executed query count varies from several hundred to many thousand.

Today, when executed particular task with very many queries, I noticed an error in the log file:
A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at MyTestApp.Program.QueryOneLineOneValue(String cmdText)
at MyTestApp.Program.Worker(String[] args)
at MyTestApp.Program.Main(String[] args)

At first, the error message lead me to think that something is wrong with the network. “Physical connection is not usable”… anyway it didn’t say anything useful… and SQL Server log file didn’t have anything useful either.

A quick Google search revealed that many people have the same problem:

  1. Minimizing Connection Pool errors in SQL Azure — it has code example with an serious bug, and it leads to Reliability Update 1 for the .NET Framework 4 which may help judging from the description – “Issue 14 – A transport-level error has occurred when sending the request to the server.” I didn’t tried it, mostly because it talks about Azure services.
  2. Discussion in the Microsoft SQL Server Database Engine forum which leads to MSDN article about SQL Server Connection Pooling which again does not help much, because I have only one connection in my application.
  3. And a couple not very useful pages from the Stack Overflow: One suggests to call ClearAllPools and another one is about Azure again.

Again, I had to find solution by myself. I looked at the code and didn’t see anything unusual. I did another run under the VS debugger, and after a while the same exception was thrown. Looking under debug log file, I noticed that program stopped in the same place that in the production server. And by the same place I mean, the same amount of SQL commands were executed.

I quickly put a counter on right before SQL command execution and found that .NET throws exception always on 32767 command. 32767 is very familiar constant for programmers, and it is obvious that some resource leakage is happening. A quick glance at the code again revealed that a call to Close or Using statement is missing.

SqlConnection conn;
   
private static void QueryOneLineOneValue(String cmdText)
{
      string q = "SELECT ... FROM cmdText...";
      SqlCommand command =
         new SqlCommand(q, conn);
   
      SqlDataReader reader = command.ExecuteReader();
   
      while (reader.Read())
      {
         //do something with the data...
      }
   
      reader.Close(); //this line was missing
}

Executing very large script on Microsoft SQL Server 2008 R2 fails

I was trying to execute very large .sql script in Microsoft SQL Server 2008 R2, that runs on Microsoft Windows Server 2008 R2.

First I tried to copy/paste script into Microsoft SQL Server Management Studio. The server has plenty of memory about 5 GB RAM, and script file is about 150 MB in size and line count is about 1 million, so I do not see problem here. Copy/paste part went ok, however after executing script and after long wait, Management Studio surrendered with “Query completed with errors”:
Microsoft SQL Server Management Studio
------------------------------
Cannot execute script.
------------------------------
ADDITIONAL INFORMATION:
Insufficient memory to continue the execution of the program. (mscorlib)
------------------------------
OK
------------------------------

bl-sql-1

Next I tried various combinations of T-SQL command line tools:
osql -S iDGZdb-dev -i test2.sql -U myuser
sqlcmd -S IDGZDB-DEV -i test2.sql -U myuser
osql -t 100000 -a 65535 ...

Still no success. Various errors appeared in console. Running locally on SQL server:
[SQL Server Native Client 10.0]
Shared Memory Provider: No process is on the other end of the pipe.
[SQL Server Native Client 10.0] Communication link failure

Running on remote machine:
[SQL Server Native Client 10.0]
TCP Provider: An existing connection was forcibly closed by the remote host.
[SQL Server Native Client 10.0]Communication link failure

Tried to search for Google and didn’t find any solution. There were many forums where users ask for help about this issue, but no single answer. Suggested “failed network equipment” is nonsense, if I run script locally, there should be no network involved.

Looked in the System event log under Application logs on the Windows Server where SQL Server resides. Luckily there was an error from the SQL Server:
Log Name: Application
Source: MSSQLSERVER
Date: 2013.02.04. 17:37:41
Event ID: 7836
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: iDGZdb-dev
Description:
A fatal error occurred while reading the input stream from the network. The maximum number of network packets in one request was exceeded. Try using bulk insert, increasing network packet size, or reducing the size of the request. The session will be terminated.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MSSQLSERVER" />
<EventID Qualifiers="49152">7836</EventID>
<Level>2</Level>
<Task>2</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2013-02-04T15:37:41.000000000Z" />
<EventRecordID>223808009</EventRecordID>
<Channel>Application</Channel>
<Computer>iDGZdb-dev</Computer>
<Security />
</System>
<EventData>
<Binary>...some hex stuff...</Binary>
</EventData>
</Event>

But unfortunately, the error reveals very little about the problem. Didn’t get this part of suggestion “… or reducing the size of the request”. The request of 150 MB is too big for the latest edition of MS SQL? Seriously?

Note: Ok, ok… Microsoft SQL Server 2012 is out, and I am not an expert in this area, however, if MySQL can handle scripts in size of gigabytes and Oracle too, then I must be doing something very wrong, but again, then where are all suggestions in the net? Even StackOverflow has only guesses about “network equipment”.

Next, I tried to increase the packet size as suggested in System log message. More statements were executed from the SQL script, however, it still stopped with a memory error:
There is insufficient system memory in resource pool 'internal' to run this query.
Network packet size (bytes): 32576

How I solved this? I surrendered, and instead of wasting time with weird T-SQL error messages, I wrote a little C# script, that executes my file line by line. All of my SQL statements were one-liners. It took me about 1/2 hour to write this script and execute it.

Here is relevant code fragment:
SqlConnection connection = new SqlConnection(Properties.Settings.Default.YourConnectionString);
connection.Open();
System.IO.StreamReader file = new System.IO.StreamReader(yourFilename);
while ((line = file.ReadLine()) != null)
{
    try
    {
        SqlCommand command = new SqlCommand(line, connection);
        command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        LogErrorSomewhere(ex.Message, line);
    }
}

file.Close();

P.S. If you know, how to execute a such script using standard SQL tools, then you are welcome to leave me a comment.

No ODBC driver after installing Oracle 11g R2

Today, after installing Oracle Database 11g R2 Client there was no ODBC driver listed under Data Sources (ODBC) window. First I tried to change Setup type to Administrator, then Instant Client, etc… noting worked.

Then I remembered my last issue with Same / different file problem on 64-bit / WoW64. As you remember, you cannot load 32-bit DLL, driver, etc. in 64-bit process. Read more about WoW64.

The problem is, that I have installed 32-bit Oracle on 64-bit Windows.

Now there are two options:

  1. Install 64-bit Oracle;
  2. If you can not do 1. (my case), then you can access 32-bit ODBC under:

    Start - Run - %windir%\SysWOW64\odbcad32.exe

Microsoft have article The 32-bit version of the ODBC Administrator tool and the 64-bit version of the ODBC Administrator tool display both the 32-bit user DSNs and the 64-bit user DSNs in a 64-bit version of the Windows operating system.