WishMesh

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

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.