Getting error while running 50 MB script on SQL Server 2008 R2

Question

I am using SQL Server 2008 R2, I have a script to update the DB, that script is approx 50 MB in size and contains some about 800,000 lines.

Error:

TITLE: Microsoft SQL Server Management Studio

Cannot execute script.

ADDITIONAL INFORMATION:

Insufficient memory to continue the execution of the program. (mscorlib)

Can somebody please help me to run this script without getting this error?

score 95 · Accepted Answer · edited Mar 27 '15 at 20:42

95

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

edited Mar 27 '15 at 20:42

rball

6,925
7
49
77

answered Jul 03 '12 at 09:11

when i tried it by using above code it is giving me some error, Msg 4060, Level 11, State 1, Server (my PC login name), Line 1 Cannot open database "(local)" requested by the login. The login failed. Msg 18456, Level 14, State 1, Server (my PC login name), Line 1 Login failed for user 'my login user name'. – Vikrant More Jul 03 '12 at 10:03
because the answer was wrong, is the database for argument "-d" – Leandro Bardelli Nov 02 '12 at 14:18
I use SQLCMD -d master -i D:\BankMaster.sql for new database – RckLN Oct 04 '13 at 19:49
1

You can also specify an output file using the -o parameter. SQLCMD -d database -i filename.sql -o result.txt – Francisco Goldenstein Mar 14 '14 at 19:30
9

With user credentials SQLCMD -s .\sqlent1 -U username -P password -d database -i script.sql – Jitendra Pancholi Jan 09 '15 at 13:04
and if you need to specify the port, it would be \, – Anssssss Jul 07 '15 at 21:55

score 62 · Answer 2 · edited Jan 23 '14 at 15:30

62

Adding to @user1293068's answer, I needed to specify the instance name. Here is my full syntax:

sqlcmd -S <ComputerName>\<InstanceName> -d <DatabaseName> -i <MyScript.sql>

This is documented on Technet's Use the sqlcmd Utility article.

(Note that you must enter a switch value of "-S", not "-s". The switch is case-sensitive.)

edited Jan 23 '14 at 15:30

Duncan C

128,072
22
173
272

answered Jan 22 '14 at 01:20

Chris Schiffhauer

17,102
15
79
88

2

example of what I used --> `C:\Users\tstick\Documents>sqlcmd -S server.subdomain.companyname.com,4184 -d ebimap -i SQLscript.sql` – Tom Stickel Jun 15 '16 at 20:31
2

It looks like the default instance name (MSSQLSERVER) needs to be excluded to connect, e.g. `sqlcmd -S localhost -d db` and not `sqlcmd -S localhost\MSSQLSERVER -d db` – BurnsBA Aug 10 '16 at 14:01
+1 I also needed to add `-x` because my script contains `$(` - from http://stackoverflow.com/a/12301822/36036 – Aximili Oct 31 '16 at 10:32
Extra +1 for pointing out the switch is case sensitive – Karsten Dec 09 '20 at 13:44

score 16 · Answer 3 · answered Mar 09 '17 at 13:17

16

If credentials are required

sqlcmd -S <ComputerName>\<InstanceName> -U <username> -P <password> -d <DatabaseName> -i <MyScript.sql>

answered Mar 09 '17 at 13:17

Jude

2,353
10
46
70

score 14 · Answer 4 · edited Apr 14 '17 at 13:41

14

You can also try increasing the Maximum Server Memory 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.

edited Apr 14 '17 at 13:41

Hakan Fıstık

16,800
14
110
131

answered Mar 04 '14 at 21:57

dstetsenko

521
4
5

Great answer! Thanks. – Mohsen Tavoosi محسن طاوسی Apr 01 '16 at 12:44
cannot verify this myself. "user does not have permission to perform this action" – Tom Stickel Jun 15 '16 at 20:41

score 6 · Answer 5 · edited Dec 22 '15 at 23:33

Ok, none of the answers were sufficient to get my script successfully restoring.

So:

Ensure that your network account has sufficient permissions to access both SQL Server instance and the specific database you intend to restore.
It's best that the database you intend to restore actually exists, if it was merely generated from Generate Scripts (with modifications to include both Schema and Data)
Run the command-line tool in Administrator mode (if necessary), e.g., type CMD from Run on the start menu
In the command-line tool, type something like "SQLCMD -d CMS -i C:\Carnotaurus\Data\script.sql". Here, CMS is the name of the database I intend to restore and "C:\Carnotaurus\Data\script.sql" is the full file path of the restore script that was generated by the Generate Scripts in SMSS.

I hope this clears-up a few missing steps.

score 1 · Answer 6 · answered Dec 22 '20 at 10:53

1

Open command Prompt

sqlcmd -S servername -i C:\Users\mycomputer\Downloads\script\yourscript.sql -d datebasename

answered Dec 22 '20 at 10:53

Muhammad Armaghan

529
6
8

Getting error while running 50 MB script on SQL Server 2008 R2

6 Answers6

Linked