Running Big SQL Files against SQL Server – Day 2

Background

Quick follow-up to our last post.  In the post we discussed that we were not able to use sqlcmd.exe to post a relatively big file to SQL Server.

File Stats

Here is the file over EmEditor:

EmEditor-FileProperties

The File’s Size is 175 MB, and it has 160, 000 lines of SQL Statements.

 

Tools

SQLCMD.EXE

StackOverflow.net

Read more online and found this hint from “How do you import a large MS SQL .sql file?“.  Here is the Link.

Takuro suggested adding the -a option.  And, passing in the max value, which is 32767.

Code


@echo on
setlocal DisableDelayedExpansion
 
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
)
 
set "_APP_FULLPATH="%_APP_FOLDER%\%_APP_NAME%""
set "_user=dbuser"
set "_password=s#mplep1"
 

set "_folderOutput=\\APPSERVER\d$\DBScripts\ProductionToDR\sqlfiles"
set "_filename=dbo.PaymentCharges-full-v3.sql"
set "_filenameFull=%_folderOutput%\%_filename%"

echo _filenameFull %_filenameFull%
 
set "_destinationServer=DBSERVERDR"
set "_destinationDatabase=HRDB"
 
set "_destinationuser=%_user%"
set "_destinationpassword=%_password%"

@REM This option corresponds to an environment variable SQLCMDPACKETSIZE.
set "SQLCMDPACKETSIZE=32767"

@REM -b
@REM Exit on error

time /T

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

time /T
goto completed
     
:BINARY_NOT_FOUND
echo %_APP_NAME% not found!
  
:completed
setlocal EnableDelayedExpansion


Explanation

  1. Environment Variables
    • SQLCMDPACKETSIZE
      • set SQLCMDPACKETSIZE=32767
  2.  Argument
    • -a option
      • In our case we are passing in the %SQLCMDPACKETSIZE% argument

Options

 

Argument Explanation
-a Option a is used to specify a packet size in the range of 512 through 32767. It has no visible effect on the output.
Increased packet size can enhance performance of a large script execution by packing more data in the envelope.
However, the provider can choose not to grant this request, in which case SQLCMD defaults to the server default packet size.
This option corresponds to an environment variable SQLCMDPACKETSIZE.
-b Terminate batch job if there is an error

 

Listening

Listening to Mali Music – Beautiful @MaliMusic

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