Press enter to see results or esc to cancel.

Execute Large SQL Script with SQLCMD

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someonePin on PinterestShare on Tumblr

Did you ever end up having a large SQL script which you tried to execute with SQL Server Management Studio? Did you come across an error message telling you that there is insufficient memory (error message: ? (see picture 1 below)

01 - SSMS - Insufficient memory

 

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot execute script.
------------------------------
ADDITIONAL INFORMATION:
Insufficient memory to continue the execution of the program. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------

In my case the generated SQL script I wanted to execute was 330MB big, which is way too large for SSMS to handle.

Fortunately there is a workaround – SQLCMD.

The Solution

The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches.

(Taken from: https://msdn.microsoft.com/en-us/library/ms162773.aspx)

SQLCMD allows us to execute sql scripts from the command prompt and guess what, it can handle large files without any problem (in my experience so far – please comment if you have other experiences) and it is installed together with SQL Server Management Studio, so there is no additional download required.

Simply open your Command Prompt (remember to start it with an account that has access to the database server) and put the following command into it:

SQLCMD -S SERVERINSTANCE -E -i yourscript.sql -o output.txt

-S defines the SQL instance on which to execute the script.
-E specifies to use a trusted connection
-i defines the SQL script to execute
-o defines to which file SQLCMD should write the output
(A full list of all available parameters can be found on https://msdn.microsoft.com/en-us/library/ms162773.aspx)

Run the command and you have accomplished executing a large SQL script.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someonePin on PinterestShare on Tumblr
Comments

Leave a Comment