Home Dashboard Directory Help
Search

SQL Server Management Studio can't handle large files by 7AE0DCEF-8457-4105-8D56-B9ABD8898AD4


Status: 

Closed
 as Won't Fix Help for as Won't Fix


43
2
Sign in
to vote
Type: Suggestion
ID: 269566
Opened: 4/9/2007 7:35:34 AM
Access Restriction: Public
Duplicates: 331214
4
Workaround(s)
view

Description

The inability of SSMS to handle large (e.g., 50MB+) script files has been significantly reduced its utility and been a hindrance to productivity. In working with large database update scripts, I was trying to take advantage of regex search and replace for enhancing the scripts. But, on a PC with 1GB RAM, I was unable to save the changes, encountering the error message "The operation could not be completed. Not enough storage is available to complete this operation.". And I was frequently unable to copy and paste usefully large portions of the changes, getting the error "The selection is too large to copy to the clipboard. Please make a smaller selection and try again."

In both scenarios, SS2000 Query Analyzer consistently handled these operations without issue -- even when SSMS (SqlWb.exe) was still running and consuming significant memory.
Details
Sign in to post a comment.
Posted by Rob Davis on 6/23/2014 at 6:20 AM
Just hit this problem with a 52mb file with 200,000 lines, each doing an IF NOT EXISTS ... INSERT INTO ... in SQL Server Management Studio 11.0.3128

Running the command line tool is not appropriate for the release processes currently in place.

How many lines can be run per execution?

Is this about number of lines, number of characters, numbers of executable statements?

I have tried SET IMPLICIT_TRANSACTIONS OFF to no avail.
Posted by Dean Grande on 3/16/2014 at 6:16 PM
This is still a problem in SSMS 2012 running on an x64 Win8 with 6GB RAM.

I have an 85MB sql script containing 14,000 insert statements.

I tried placing a GO after each 100 statements and it did not help, which was surprising. I understood that the GO statement was used (ie. parsed) by SSMS so that it was effectively submitting smaller chunks to the server for execution. source: http://technet.microsoft.com/en-us/library/ms188037.aspx

I get an error when I try to execute the whole script.

The only way I can run the script is to highlight a few hundred rows and execute just those (which is time consuming).


Help -> About
-------------------------
Microsoft SQL Server Management Studio                        11.0.3128.0
Microsoft Analysis Services Client Tools                        11.0.3128.0
Microsoft Data Access Components (MDAC)                        6.3.9600.16384
Microsoft MSXML                        3.0 4.0 6.0
Microsoft Internet Explorer                        9.11.9600.16521
Microsoft .NET Framework                        4.0.30319.34011
Operating System                        6.3.9600

Posted by Craig Humphrey on 10/14/2012 at 8:09 PM
This is still a problem in SSMS 2008R2 SP1 running on an x86 Win7 with 4Gig RAM.

In my case, I generated an ALTER script from an SSAS database, which was 300,000+ lines and now I can't save it.

Worked just fine on a x64 machine with 4Gig RAM however.
Posted by Jason88 on 4/17/2012 at 9:28 AM
you had a problem with a 50MB file? consider yourself lucky, my script is only 864KB, with a large update of like 12k lines, as soon as I load the script, SSMS dies
Posted by tofutim on 8/31/2011 at 1:19 PM
As for me, I had a script (350MB) that I was able to load into SSMS and edit. However, I could not save the said sql to be able to run SqlCmd. Out of storage! Do I really have to resort to editing using XEmacs?
Posted by Microsoft on 5/10/2011 at 3:32 PM
Hi everyone -
Thanks for writing in. I'm the new program manager in charge of this area. We have done some looking in to this and, unfortunately, it's not something that's going to get fixed right away. That being said we are looking at a full fix in a future release. We understand that there are a number of limitations around large files that we'd like to fix once and for all.

As far as the 'won't fix', in this case, it means we have added a feature to our backlog to fix this. I hope that helps answer the question. We do also appreciate the up votes. That helped make the case for why this is important.

Thanks
-Sam Hughes
Posted by DB007 on 2/8/2011 at 2:44 AM
We are told to "watch this space" this then gets marked as: Resolved -as Won't Fix -??

It is a problem, even on large memory machines. SSMS has not been designed to work with large open files, when compared to the old SQL Query Analyser no problems were encountered with large files.
Its been more of the case of two steps forward, three backwards when moving from SQL QA -> SSMS.
Posted by SQLHawk on 8/5/2010 at 7:38 AM
Is there a fix for this issue yet? It's been over 3 years since this was first reported.... how long will it take to see a fix?
Posted by rhchin on 1/8/2010 at 1:01 PM
Same problem here:

===================================
Cannot execute script.
===================================

Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)

------------------------------
Program Location:

at Microsoft.Win32.Win32Native.CopyMemoryUni(StringBuilder pdst, IntPtr psrc, IntPtr sizetcb)
at System.Runtime.InteropServices.Marshal.PtrToStringUni(IntPtr ptr)
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextBuffer.GetText(Int32 startPosition, Int32 chars)
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextBuffer.get_Text()
at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.GetSelectedTextSpan()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptEditorControl.GetSelectedTextSpan()
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptAndResultsEditorControl.OnExecScript(Object sender, EventArgs a)

And when SQLCMD is used:

C:\>sqlcmd -E -S<ServerName> -d<dbName> -iD:\<inputfile>
Changed database context to '<dbName>'.
Sqlcmd: Error: Scripting error.
Posted by mpavlik on 11/7/2009 at 8:05 AM
I am using MS SQL 2005 64-bit edition SP3 on Windows 7 with 6GB RAM. Task manager says I am only using 2.5 GB RAM, but I get the following error when trying to run a 180MB file:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot execute script.

------------------------------
ADDITIONAL INFORMATION:

Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib)


When will this be fixed?
Posted by Hopelessly Optimistic on 3/30/2009 at 7:43 AM
I encountered this message both in SSMS & SQLCMD while executing a SQL script with over 100K results. Is there a work around besides breaking my query into smaller chunks?
Posted by Lyme on 3/7/2008 at 8:44 PM
I have encountered the same problem http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2936842&SiteID=1
Posted by Microsoft on 4/9/2007 at 10:53 PM
Hi rthorington,

Thanks for reporting this issue. We will be considering to fix this issue in our future releases. Watch out this space for further updates.

Regards
Vinod
Sign in to post a workaround.
Posted by AgeofMachines on 12/8/2012 at 1:09 PM
Try running it in SSIS. You can run script files in the package using the Execute Task object. Set the SQLSourceType to File Connection and then set the File Connection to a new file connection that points to your script file.

Make sure you have some decent error handling in there or it can be difficult to debug.
Posted by Craig Humphrey on 10/14/2012 at 8:09 PM
Run SSMS in an x64 environment.
Posted by Vertongen on 3/4/2010 at 7:28 AM
You could use sqlcmd to execute your query from a sql file: http://msdn.microsoft.com/en-us/library/ms165702.aspx
Posted by Benjamin Birney on 1/13/2010 at 7:33 AM
Splitting the SQL statements into several chunks usually works. Pick a good spot in the file, obviously, like immediately after an INSERT or some other structural change. Just select each set of commands you want to execute, hit Execute or Alt-X, then select the next set, rinse and repeat. You can work your way through a large SQL file this way.