SQL server 2008 running large query - out of memory

Question

I've got a 140mb SQL query to import data to a new database. I file > open the query, and try and execute it but I get:

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

I got 8gb ram, using <3gb so what can I do?

   at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
   at System.Text.StringBuilder..ctor(String value, Int32 startIndex, Int32 length, Int32 capacity)
   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)

 

Microsoft SQL Server Management Studio                      10.50.1600.1
Microsoft Data Access Components (MDAC)                     6.1.7600.16385
Microsoft MSXML                     3.0 6.0 
Microsoft Internet Explorer                     9.0.8112.16421
Microsoft .NET Framework                        2.0.50727.4952
Operating System                        6.1.7600

Answer 1

This is a known issue reported on Microsoft Connect:

http://connect.microsoft.com/SQLServer/feedback/details/269566/sql-server-management-studio-cant-handle-large-files

If you take a look you can see some workarounds such as using SQL CMD to execute and editing in Notepad++.

It does not appear they will be fixing this however.