3

I have a sql file, which is up to 180 Mb. When I try to open and execute it on Microsoft SQL Server Managment Studio 2012, I am getting this error. enter image description here . What do do ? I researched on Google. And there were a few answers. One solution they say was running a C# script. Here is it . But I don't know how to run, and anyway if that script will work or not. Thanks for help.

  • What does this SQL file contain? Are you trying to import data in table? – gotqn Aug 15 '14 at 9:59
3

Management Studio needs more memory than just 180MB to open such a file - it's not just storing the entire text of the file in memory, but also loading any required objects for IntelliSense, performing all kinds of parsing and validation, etc. You may have slightly better luck if you turn IntelliSense off, but some of those activities will still happen. Especially if it's all one batch.

The C# approach may work okay for this file, but I suspect it isn't written in such a way that it will always know how to parse every single file you might present - think about cases where you declare variables, use #temp tables, etc. and . You will probably be far better off:

  1. splitting the file up into multiple files;
  2. sprinkling a healthy does of GO batch separators to stop Management Studio from treating the script as one giant batch;
  3. constructing the file in such a way that you can use BULK INSERT, bcp, SSIS or import/export instead of opening it in Management Studio; or,
  4. constructing the individual statements differently. An example - I see a lot of big import files with individual insert lines like this:

    INSERT INTO dbo.tablename(col,col,col) VALUES(value,value,value);
    INSERT INTO dbo.tablename(col,col,col) VALUES(value,value,value);
    INSERT INTO dbo.tablename(col,col,col) VALUES(value,value,value);
    INSERT INTO dbo.tablename(col,col,col) VALUES(value,value,value);
    

    You can save a huge amount of space in the sheer size of the file by doing this instead:

    INSERT INTO dbo.tablename(col,col,col) VALUES(value,value,value),
      (value,value,value),(value,value,value),(value,value,value);
    

    If your current file is all insert statements, this may make a huge dent; however you do need to be aware that you shouldn't group more than about 1,000 sets together.

    That alone probably isn't enough to solve the problem, but it's still a better strategy in general IMHO.

I'd also recommend getting the most recent version (if you are using Express, get it here).

You can see other suggestions here.

4

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd

This did the trick for me:

sqlcmd -S serverName\instanceName -i script.sql  -o output.txt
1

That C# script reads the file and executes each line from it. That works fine beacause the file contains only one line statements. If your T-SQL structure from your file is the same, then that C# code will work for you. You can check that by opening the file with Notepadd++.

Cheers,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.