Have you ever encountered an error message in SQL Server Management Studio (SSMS) stating “Cannot execute script. Insufficient memory to continue the execution of the program. (mscorlib)” when trying to execute a large script file? This problem exists even in SQL Server 2014 and can be frustrating to deal with. However, there is a solution to overcome this issue by using the SQLCMD utility from the command line instead of SSMS.

The Problem

Let’s say you have a large T-SQL script file that you want to execute in SSMS. However, when you try to execute it, you receive the error mentioned above. This occurs because SSMS is unable to handle large script files efficiently.

The Solution

An alternative to SSMS is to use the SQLCMD utility from the command line to execute the script. SQLCMD is a utility that allows you to execute T-SQL statements, script files, and stored procedures from the command line.

Unlike SSMS, which uses the Microsoft .NET Framework SqlClient for execution, SQLCMD uses the ODBC driver when run from the command line. To execute a script file using SQLCMD, you can use the following syntax:

sqlcmd -S <server_name>\<instance_name> -i "C:\path\to\script.sql"

For example, if your server name is “DBSERVER”, instance name is “TESTINSTANCE”, and the script file is located at “C:\Scripts\script.sql”, the command would be:

sqlcmd -S DBSERVER\TESTINSTANCE -i "C:\Scripts\script.sql"

By running this command, the script file will be executed, and you can see the progress and completion in the command prompt. Once the script is successfully executed, you can verify the results in SSMS.

Conclusion

SQL Server Management Studio may not be suitable for executing large script files due to memory limitations. However, when dealing with large files, such as loading a significant amount of data or executing create database scripts generated from large databases, using the SQLCMD utility from the command prompt can help overcome these issues. It provides a more efficient way to execute large script files and ensures successful execution without running into memory constraints.