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?!”
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
-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.