Search

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

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

36
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)
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 (expand)
Product Language
English

Category

Tools

Proposed Solution

Handle memory in SSMS in a manner that enables normal/performant editing, navigation, search and replace, and saving of large scripts as effectively as competing tools (including MS own prior-generation tools) allow, and more proportionally to the available memory.


Thank you.

Benefits

Faster Development
Improved Reliability
Improved User Interface
Improved Administration

Other Benefits

 
File Attachments
0 attachments
Sign in to post a comment.
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.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement