Tweet about this on TwitterShare on FacebookShare on Google+Share on StumbleUponShare on TumblrShare on RedditPin on PinterestEmail this to someone

Especially when you migrate from one database to another and want to transfer data of certain tables using scripts, you may want to generate sql scripts from existing data tables and run it on new database. Most of the time these tables have lot of data records hence the generated scripts will be huge. Easiest way of executing them is using the SQL Server Management Studio script editor. Problem with large scripts is when you run them more often it throws “Cannot execute script Insufficient memory” error.

Cannot Execute Script Insufficient Memory Error

Cannot Execute Script Insufficient Memory Error

Only option I found for this is to switch to command line tool to run the script. There are two tools based on the version of Sql Server and client tools installed on your machine. But both of them have similar syntax so you can use either one of them. One is osql.exe and other one is sqlcmd.exe. If you’re using latest version of Sql Server (2005 or later) it’s better to use sqlcmd.

I will explain one but same set of parameters can be applied to other as well.

I assume we need to run this on “localhost” and on db called “mydb” with trusted connection.

osql –S localhost –d mydb –E –i .\hugescript.sql

If you need to provide Sql authentication then use -U and -P to provide database username and password. This will solve the memory issue and can run fairly large scripts. Using sqlcmd I was able to run a script of size 80MB (of course it took hours to complete as it is connected to Sql Azure database).

If you want to run the script against Sql Azure database then the syntax would be as follows.

sqlcmd -U dbuser@sqlazureservername.database.windows.net -P dbuserpassword -S sqlazureservername.database.windows.net -d databasename -i script.sql

Make sure that user name is given in above format ‘user@servername’.

 

Tweet about this on TwitterShare on FacebookShare on Google+Share on StumbleUponShare on TumblrShare on RedditPin on PinterestEmail this to someone