In this article, we will learn how to solve System.OutOfMemoryException error while executing scripts in SQL Server Management Studio.
It is ok to execute average size of the script using SQL Server Management Studio but If you want to execute a large SQL script in SQL Server Management Studio but ended up with an error as given below
Cannot execute script
Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)
Cannot execute script. Insufficient memory to continue the execution of the program. (mscorlib)
This error caused because SQL Server Management Studio has insufficient memory to allocate for large results. You do not need to worry. we will learn how to overcome this error with some simple tips.
Solution: Use SQLCMD
If you have installed any version of SQL SERVER, you can simply use sqlcmd. In SQL Server, the sqlcmd utility is a command-line tool that lets the user submit T-SQL scripts on local and on remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing.
Open the Command Prompt as an administrator and execute below command with replacing properties name as per your sql server configuration and script file name.
[su_note note_color=”#fdf6d5″]sqlcmd -S servername\instancename -i D:\yoursqlscript.sql[/su_note]
if you are getting an error – “Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user ‘servername\instancename'” after executing above command. Don’t worry you can simply overcome this error by providing username and password which you have provided for authentication for SQL SERVER. You can use below command
[su_note note_color=”#fdf6d5″]sqlcmd -S servername\instancename -U sa -P YourSQLServerPassword -i D:\yoursqlscript.sql[/su_note]
Note:- There parameter “–U, -P, –i” are case sensitive parameter. Do not use -U and -P as a small letter as -u, -p and i as -I because the sqlcmd utility has reserver function for that particular parameter.
- -U (for login id )
- -P (for password)
- -u (for unicode output)
- -p (for print statistics[colon format])
You can learn more about all the available parameter of sqlcmd utility by using below command
[su_note note_color=”#fdf6d5″]sqlcmd /? or sqlcmd help [/su_note]