Running Big SQL Files against SQL Server

Background

Using Tablediff, Generated a file of about 150 MB.

Now ready to train the SQL file against our DR Server, but no play.

 

Error Message

Client

Textual


HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

Image

TCPProvider-AnExistingConnectionWasForciblyClosedByTheRemoteHost

Server

Textual


A fatal error occurred while reading the input stream from the network. The session will be terminated.

Image

AFatalErrorOccurredWholeReadingTheInputStreamFromTheNetwork

 

File Size

The file size is about 170 MB.

filesize

 

Editor

Because of the file’s size, there are seemingly few editors that is able to handle text files of such sizes.

EmuraSoft – EmEditor

EmEditor

EmEditor is available @ here.

Tool

SQLCMD.EXE

Btw, the tool we were attempting is sqlcmd.exe

 

OSQL.EXE

Based on a couple of Stack Overflow QA Entries tried OSQL.

  1. How do you import a large MS SQL Server File ( Link )
  2. Executing a very large SQL File ( Link )

 

Code


@eecho on
setlocal DisableDelayedExpansion

set "_APP_NAME=osql.exe"

if exist "D:\Program Files\Microsoft SQL Server\120\Tools\Binn" (
set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\120\Tools\Binn"
)else if exist "D:\Program Files\Microsoft SQL Server\90\Tools\binn" (
set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\90\Tools\binn"
)else (
goto BINARY_NOT_FOUND
)

set "_APP_FULLPATH="%_APP_FOLDER%\%_APP_NAME%""
set "_user=dbuser"
set "_password=unsecuredpwd"

set "_folderOutput=\\TYPHOON\d$\DBScripts\ProductionToDR\sqlfiles"
set "_filename=dbo.paymentCharges.txt"
set "_filenameFull=%_folderOutput%\%_filename%"

echo _filenameFull %_filenameFull%

set "_destinationServer=MSSQLDR"
set "_destinationDatabase=HRDB"

set "_destinationuser=%_user%"
set "_destinationpassword=%_password%"

time /T

@REM Running Transact-SQL Script Files Using osql
@rem use -n Remove Number :- to suppress batch numbers

%_APP_FULLPATH%^
-S %_destinationServer% -d%_destinationDatabase% ^
-U %_destinationuser% -P%_destinationpassword% ^
-i %_filenameFull%

goto completed

 

Osql – Options

Echo Line Number

The current line number is displayed on the client…

suppressLineNumbering-0743PM

If you find it obstruction, please use use -n option,  to suppress batch numbers

 

Osql – Sessions

Forcibly Closing

By the way, it does not appear that one can forcibly terminate an osql session via clicking on CTRL-Break on the Command Window.  One can initiate closure by launching Task Manager, look for the process, and terminate it that way.

Version

Version Matrix

Depending on directory\folder where the tools are installed, we can determine where SQL Server tool we are targeting.

 

Database Compatibility Level Marketing Product Version
120 SQL Server 2014 12.00.xxxx
110 SQL Server 2012 11.00.xxxx
 105  SQL Server 2008 R2 10.50.xxxx
 100 SQL Server 2008 10.00.xxxx
90 SQL Server 2005 9.00.xxxx
80 SQL Server 2000 8.00.xxxx

 

OSQL

Btw, we are running osql version 120, which is SQL Server 2014.

osql-version-120

 

SQLCMD

Code


set "_APP_NAME=sqlcmd.exe"
 
if exist "D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn" (
    set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
)else if exist "C:\Program Files\Microsoft SQL Server\90\Tools\binn" (
    set "_APP_FOLDER=C:\Program Files\Microsoft SQL Server\90\Tools\binn"	
)else if exist "D:\Program Files\Microsoft SQL Server\90\Tools\binn" (
    set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\90\Tools\binn"
)else (
    goto BINARY_NOT_FOUND
)

Interesting enough, sqlcmd is installed in the folder D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn.

From the matrix above, we can deduct that we are using SQL Server 2012. But, interesting enough, it is targeting v2014.

Please read more here.

 

Additional Checks & Customizations

  1. Add “no count” at beginning of the file
  2. Temporarily disable
    • Non-clustered indexes
    • Same with Foreign keys
    • DDL Triggers

 

Listening

Listening to Justin Bieber loving himself.

 

References

  1. SQLCMD
  2. CodePlex – Big SQL Script File Runner

One thought on “Running Big SQL Files against SQL Server

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s