Code Highlighting

Monday, October 22, 2012

SSMS: OutOfMemoryException executing a large SQL batch

So you're executing some beast of an Sql batch using the Management Studio, for instance the result of "Generate Scripts" on another database. Then all of a sudden you get a OutOfMemoryException. Even when just keeping the entire script in memory, poor SSMS is hanging on by its fingernails. How could you expect it to also execute that file, and give you the results?

Thankfully there is a command-line Sql server client Microsoft thoughtfully provides together with Sql server. If you're not able to import a large sql file using SSMS, navigate to the Binn folder of your Sql server folder (C:\Program Files\Microsoft SQL Server\100\Tools\Binn here) in Command Prompt, and type the following:

osql -S databaseserver -U username -P password -d DemoDatabase -i c:\demo.sql

That executes the file C:\demo.sql on databaseserver in the context of DemoDatabase using the login data provided. This will scroll a bunch of query results in your command prompt window. If you'd rather examine these results in detail later, the -o parameter writes this info to an output file:

osql -S databaseserver -U username -P password -d DemoDatabase -i c:\demo.sql -o c:\output.txt

That should work, even when SSMS chokes on the sheer size of your query file.

No comments:

Post a Comment