Alternative Approach: Using SQLCmd to Execute a Large Script

Scenario: You’ve scripted the data of a largish table to a file (perhaps you need to deploy a solution you’ve built and SSIS is not expedient). You’re all set to execute your script in SSMS when…”Gadzooks!…an ‘out of memory’ error?!”

InsertFileTooBigForSSMS

“Out of Memory Exception”

Solution: Use the sqlcmd command-line utility to execute the script. What ostensibly overwhelmed my RAM in SSMS above works using sqlcmd. Here’s an example script:

sqlcmd -E -d AdventureWorks2008R2 -i D:\DBA\Misc\ProductBloatInsert.sql -o D:\DBA\Misc\ProductBloatOutput.txt

Where:

-E: indicates a trusted connection

-d: is your target database name

-i: is your bloated script file you want to execute

-o: is an output file (very helpful to catch errors)

See “sqlcmd utility” for exhaustive treatment.

Conclusion: When confronted with a largish script file that overwhelms SSMS, try using the sqlcmd utility as an alternative.

This entry was posted in Alphabetical, Alternative Approach and tagged , , , . Bookmark the permalink.

2 Responses to Alternative Approach: Using SQLCmd to Execute a Large Script

  1. Jack Vamvas says:

    Your example shows output to a log file. It is sometimes useful to output to the screen and log file – check this example – http://www.sqlserver-dba.com/2012/09/sql-server-sqlcmd-with-screen-output-and-output-file.html

Comments are closed.