Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

444 Posts

Posted - 07/07/2014 :  06:14:28  Show Profile  Reply with Quote

I have a large database with a table of 200 Million records stored in SQL Server 2008 Standard.

The server that holds the SQL Server has the following specs;
Windows Server 2008 R2 Standard 64-bit.
4 CPU's
Memory = 500 GB with 220 GB used by the database.

SQL Server
SQL Server 2008 Standard Edition

At runtime when I try to open a column from the table;


select ConsultationGuidDigest from dbo.Consultation

It failed at record 33,554,432 and throws the error message

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

I googled and the option of output the results as text - same issue
Output the results to a file - is not an option I want to go ahead with with the tasks I have.

If, I need to upgrade the specs for SQL Server or do anything to accomodate this so that the tables could open ok - please advice

Thank you in advance

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 07/07/2014 :  11:58:26  Show Profile  Reply with Quote
This is not a limitation of SQL Server; it is a limitation of SQL Server Management Studio which is a 32 bit application. What is the purpose of sending 33 million records to the output window of SQL Server Management Studio? I would venture a guess that Microsoft has not tried to improve or increase the capacity of SSMS to output that much volume of data because they couldn't forsee a reason for someone to scroll through that much data in a SSMS window.

If outputting to file is not an option, you can try the other alternative - using SQLCMD.
Go to Top of Page

Almighty SQL Goddess

38200 Posts

Posted - 07/08/2014 :  12:40:36  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why are you returning all rows of this table to the client? Use bcp if you need all the data for some other task.

Tara Kizer
SQL Server MVP since 2007
Go to Top of Page

Starting Member

3 Posts

Posted - 07/09/2014 :  03:40:57  Show Profile  Reply with Quote
It's a limitation on SSMS and not with SQL Server. Try restarting the machine hosting SSMS.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000