Solved

how to run a large SQL Query?   "Insufficient memory"

Posted on 2013-06-05
9
538 Views
Last Modified: 2013-06-05
I have a 50MB .sql file.  It loads easily enough into SQL Management studio but I cannot execute it, it spits out an error that there is insufficient memory to parse the script.   Any ideas how I can run this?

SQL 2008
0
Comment
Question by:arthurh88
  • 4
  • 3
  • 2
9 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39223741
Just guessing here: If you have embedded INSERT statements, why not change your processing to load your data into a text file and BCP it into SQL.

I've been in DBA and SQL work for over 35 years, and I have yet to write a SQL file that has reached 1MB. What are you doing that must make this 50MB?
0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39223746
Couple of things that come to mind..

Split the script into "bite-sized" pieces.

50 MB SQL File?  Are you inserting or creating a ton of data?  It would be recommended to insert or link to the other database if you are using temp tables.

SELECT TOP 1 from Table
would trim down the results.

HTH,

Kent
0
 

Author Comment

by:arthurh88
ID: 39223747
yes they are 80,000 insert statements.   i dont know what you mean by BCP it or how to load it into a text file, other than renaming it to .txt....?

it is a ZIP CODE database that i just purchased for 140 bucks.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:arthurh88
ID: 39223752
i guess i could cut and paste chunks of it at a time, but PLEASE tell me there is an easier way lol
0
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 39223759
You can split the file into parts, perhaps each 10-15 MB in Size. If you have any CREATE TABLE or statements at the top other than INSERTs, which prep the way, leave them. Then break at 20000 inserts. Then create a 2nd file with the next 20000 inserts, and so on. Execute them one after the other.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39223769
Did you get your database (that you purchased) by chance just like that SQL, or did they also give you an Access version of it? Because if you got an Access database of it, you can export that right into SQL Server.
0
 
LVL 17

Expert Comment

by:Kent Dyer
ID: 39223784
BCP would be good..  Another way would be OSQL..  For example:

osql -SINSTANCE\SERVERS -Uuser -Ppassword -n -iyour_sql_file.sql -oresult.log -w250

Open in new window


You could do it as part of a DTSX package to import.

HTH,

Kent
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39223794
If OSQL blows, try SQLCMD. Use everything kdyer showed you in his code, just substitute sqlcmd for osql.
0
 

Author Closing Comment

by:arthurh88
ID: 39224172
this is exactly what im doing, and its working...although....i dont like the manual work lol.  but yes, thank you.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 40
T-SQL: "HAVING CASE" Clause 1 25
Help in Bulk Insert 9 34
SQL Server Connection String through a VPN 8 24
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question