CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2
Vikrant More
Vikrant More

Reputation: 5442

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

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?

Upvotes: 64

Views: 69160

Answers (6)

Muhammad Armaghan
Muhammad Armaghan

Reputation: 629

Open command Prompt

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

Upvotes: 1

dstetsenko
dstetsenko

Reputation: 541

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.

Upvotes: 15

Jude
Jude

Reputation: 2433

If credentials are required

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

Upvotes: 16

Phil Helix
Phil Helix

Reputation: 3733

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

So:

  1. Ensure that your network account has sufficient permissions to access both SQL Server instance and the specific database you intend to restore.

  2. 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)

  3. Run the command-line tool in Administrator mode (if necessary), e.g., type CMD from Run on the start menu

  4. 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.

Upvotes: 6

user1293068
user1293068

Reputation:

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

Upvotes: 97

Chris Schiffhauer
Chris Schiffhauer

Reputation: 17310

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.)

Upvotes: 62

Related Questions