Cannot execute script: Insufficient memory to continue the execution of the program

Question

I have a 123MB sql file which I need to execute in my local PC. But I am getting

Cannot execute script: Insufficient memory to continue the execution of the program

How to solve this issue?

Coseismal

@marc_s When I click Ctlr+E it immediately shows this error – Coseismal

Possible duplicate of Getting error while running 50 MB script on SQL Server 2008 R2 – Neurophysiology

Answer 1

use the command-line tool SQLCMD which is much leaner on memory. It is as simple as:

SQLCMD -d <database-name> -i filename.sql

You need valid credentials to access your SQL Server instance or even to access a database

Taken from here.

Sexennial

With full credentials, specifying db and server: sqlcmd -S <YOUR-SERVER> -U <YOUR-USER> -P <YOUR-PASSWORD> -d <YOUR-DATABASE> -i <YOUR-SQL-FILE-PATH.sql> – Protozoan

I get this issue: "The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values." – Decker

add -e for trusted connection – Nuno

Answer 2

It might help you! Please see below steps.

sqlcmd -S server-name -d database-name -i script.sql

• Open cmd.exe as Administrator.
• Create Documents directory.
• Put your SQL Script file(script.sql) in the documents folder.
• Type query with sqlcmd, server-name, database-name and script-file-name as like above highlighted query or below command line screen.

Proa

You can Aad the username -sa and password -123 , sqlcmd -S viaserver -U sa -P 123 -d TelstraDeployed -i SQLQuery1.sql – Proa

it give me error Sqlcmd: Error: Mircorsoft SQL Server Native Client 10.0 : Client unable to establish connection due to prelogin failure. – Sarina

Answer 3

For Windows Authentication use this sql cmd

SQLCMD -S TestSQLServer\SQLEXPRESS  -d AdventureWorks2018 -i "d:\document\sql document\script.sql"

Note: If there is any space in the sql file path then use " (Quotation marks) "

For SQL Server Authentication use this sql cmd

SQLCMD -S TestSQLServer\SQLEXPRESS -U sa -P sasa  -d AdventureWorks2018 -i "d:\document\sql document\script.sql"

-S TestSQLServer\SQLEXPRESS: Here specify SQL Server Name

-U sa: Username (in case of SQL Server Authentication)

-P sasa: Password (in case of SQL Server Authentication)

-d AdventureWorks2018: Database Name come here

-i "d:\document\sql document\script.sql": File Path of SQLFile

Froude

Answer 4

You can also simply increase the Minimum memory per query value in server properties. To edit this setting, right click on server name and select Properties > Memory tab.

I encountered this error trying to execute a 30MB SQL script in SSMS 2012. After increasing the value from 1024MB to 2048MB I was able to run the script.

(This is the same answer I provided here)

Benelux

Answer 5

My database was larger than 500mb, I then used the following

C:\Windows>sqlcmd -S SERVERNAME -U USERNAME -P PASSWORD -d DATABASE -i "C:\FILE.sql"

It loaded everything including SP's

*NB: Run the cmd as Administrator

Worden

works perfectly. Thanks. Note: -i C:\FILE.sql must be in quotes. -i "C:\FILE.sql" – Snowden

Answer 6

If I understand your problem correctly, you are trying to restore (transact sql) xyz.sql - database + schema. You can try this command which worked for me:

SQLCMD -U sa -i xyz.sql

Ilona

Answer 7

Try this step,

1)Open PowerShell

2)Write this command:

sqlcmd -S PCNAME\SQLEXPRESS -U user -P password -d databanse_name -i C:\script.sql

3)Press Return

:-)

Reddin

Answer 8

Below script works perfectly:

sqlcmd -s Server_name -d Database_name -E -i c:\Temp\Recovery_script.sql -x

Symptoms:

When executing a recovery script with sqlcmd utility, the ‘Sqlcmd: Error: Syntax error at line XYZ near command ‘X’ in file ‘file_name.sql’.’ error is encountered.

Cause:

This is a sqlcmd utility limitation. If the SQL script contains dollar sign ($) in any form, the utility is unable to properly execute the script, since it is substituting all variables automatically by default.

Resolution:

In order to execute script that has a dollar ($) sign in any form, it is necessary to add “-x” parameter to the command line.

e.g.

Original: sqlcmd -s Server_name -d Database_name -E -i c:\Temp\Recovery_script.sql

Fixed: sqlcmd -s Server_name -d Database_name -E -i c:\Temp\Recovery_script.sql -x

Altair

$ comment was very helpful. Thanks – Rumrunner

Answer 9

Sometimes, due to the heavy size of the script and data, we encounter this type of error. Server needs sufficient memory to execute and give the result. We can simply increase the memory size, per query.

You just need to go to the sql server properties > Memory tab (left side)> Now set the maximum memory limit you want to add.

Also, there is an option at the top, "Results to text", which consume less memory as compare to option "Results to grid", we can also go for Result to Text for less memory execution.

Went

Answer 10

sqlcmd -S mamxxxxxmu\sqlserverr -U sa -P x1123 -d QLDB -i D:\qldbscript.sql

• Open command prompt in run as administrator

• enter above command

"mamxxxxxmu" is computer name "sqlserverr" is server name "sa" is username of server "x1123" is password of server "QLDB" is database name "D:\qldbscript.sql" is sql script file to execute in database

Bajaj

Answer 11

If you need to connect to LocalDB during development, you can use:

sqlcmd -S "(localdb)\MSSQLLocalDB" -d dbname -i file.sql

Kean

Answer 12

As in most answers given here use the command-line tool. In my case the script already has database creation code. If your script contains CREATE DATABASE command, for example

USE [master]
GO
CREATE DATABASE [your-database-name]

Then do not use the -d your-database-name, instead use the following command.

For Windows Authentication use the command

sqlcmd -S ServerName\InstanceName -i "script.sql" -x

For SQL Server Authentication use the command

sqlcmd -S ServerName\InstanceName -U usename -P password -i "script.sql" -x

Dormeuse

Answer 13

Run Administrator CMD

SQLCMD -S ServerName -U Username -P Password -d DBName -i "E:\DB\DatabaseScript.sql"

this command is working For Me.

Shammer

The SQLCMD command for SQL Server Authentication was already answered by Manjunath Billwar. Your answer does not add anything substantial, so consider removing it. – Tarratarradiddle