Execute Script Error – Imagine my surprise when I tried executing a rather large SQL script (255 Mb) and received an error telling me that there was ‘Insufficient memory to continue the execution of the program. (mscorlib)’. You might be wondering why I would even have such a large script to begin with.
Looking for something else? Try these links instead:
- Prevent Accidental Table Data Deletion In SQL Server Management Studio
- Automate Standard Comment Blocks For New SQL Queries
- Azure Databases – Restore Local Database To Azure
- Easily Generate Table Change Scripts In SQL Server Management Studio
- ApexSQL Provides Excellent SQL Tools For Free
Well I had to import data from a file that I didn’t have access to any more, so I decided to script the table contents from the live database and import into my dev database for testing. Trying to run the query produced the error and I had to find an alternative way to get the data into my table.
Execute Script Error – Workaround
Ok, so I guess this isn’t a workaround per se but a very definite method for running SQL scripts outside SQL Server Management Studio by running the script from the command-line.
What I did next was to copy the script to my temp folder. You can see that it is rather big (for basically a text file).
I then open the command prompt (it is wise to run as Administrator here) and then I changed the directory to the following:
C:Program FilesMicrosoft SQL Server100ToolsBinn
The next command I typed in was the following:
osql -S VM-PC -d SysproCompany0 -E -i C:tempbig_sql.sql
What this means is the following.
-S is your SQL server IP Address or Name
-d is the database name to run the script against
-E means trusted connect
So think of the command as follows:
osql -S [SQL Server IP/Name] -d [Database Name] -E -i [path to your sql script file]
When you execute this command, your script will be executed against the database you selected in your command. This is a nice and easy (not to mention quick) method for executing large scripts into a database. It also happens to be really easy on memory.
