Tag Archives: Windows Server

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.

Logon user into Windows Server 2008 R2 automatically

To bypass log on screen in Microsoft Windows 2008 R2, run the following .reg file:
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
"AutoAdminLogon"="1"
"DefaultUserName"="YourUserName"
"DefaultPassword"="YourPassword"

You must change YourUserName to your desired user name, and YourPassword to corresponding password.

To import .reg file use:
regedit.exe /s filename.reg

This will import registry values without confirm dialog box. More on importing .reg.

Source taken and more options / registry values: Automatic Logon from windowsnetworking.com

Tested on Microsoft Windows 2008 R2 (Datacenter and Webserver editions).

IIS 7.5 error: Handler “PageHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list

Today I installed Clean Windows Web Server 2008 R2 64-bit with IIS 7.5. To my surprise opening .NET 4.0 application I received the following error:


IIS 7.5 Detailed Error - 500.21 - Internal Server Error

Server Error in Application "DEFAULT WEB SITE"
Internet Information Services 7.5

[Error Summary]
HTTP Error 500.21 - Internal Server Error
Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list

[Detailed Error Information]
Module IIS Web Core
Notification ExecuteRequestHandler
Handler PageHandlerFactory-Integrated
Error Code 0x8007000d
Requested URL http://localhost:80/default.aspx
Physical Path C:\inetpub\wwwroot\default.aspx
Logon Method Anonymous
Logon User Anonymous

[Most likely causes:]
•Managed handler is used; however, ASP.NET is not installed or is not installed completely.
•There is a typographical error in the configuration for the handler module list.

[Things you can try:]
•Install ASP.NET if you are using managed handler.
•Ensure that the handler module's name is specified correctly. Module names are case-sensitive and use the format modules="StaticFileModule, DefaultDocumentModule, DirectoryListingModule".

[Links and More Information]
IIS core does not recognize the module.
View more information »

As it turns out, some glitch caused this problem, and somehow .NET was not registered with IIS.

Running the following commands solved this issue:

%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -i
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -i