SQL Server: Cannot execute script: Insufficient memory on SMSS

Have you ever try executing a large script file in SQL Server Management Studio (SSMS) and get this error - "Cannot execute script. Insufficient memory to continue the execution of the program. (mscorlib)" ? 

The problem is that SSMS is unable to handle large script files and this problem exists even in SQL Server 2014. 

Solution

By using the SQLCMD utility from the command line instead of SSMS,

The SQLCMD utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job. This utility uses ODBC to execute Transact-SQL batches.  Source

To run the script file

1. Open a command Prompt Window.
2. In the Command Prompt Window, type: sqlcmd -S myServer\instanceName -i c:\myScript.sql
3. Press ENTER.

For complete information of SQLCMD, visit this page: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility


Do you have many SQL scripts to run and tired of writing in the command prompt?

RunSQLScripts is a program that allow you run all your selected SQL scripts.

1. Open RunSQLScripts
2. Fill all required information
1. Server Name - e.g myServer\instanceName
2. Database Name - your database name
3. Username & Password - the one you use to log-in.
   -"If you don't have Username & Password, just create one on Logins under Security Tab and make sure your server authentication in server properties is on SQL Server and Windows Authentication mode"
3. Add all your *.sql file.
4. Click Start

Download RunSQLScripts here: DOWNLOAD LINK