Out of Memory Error when running a Select Statement

Hi all,

In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB.

This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error.

The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total.

Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory?

Regards

Mark:

You might want to temper opening large tables with Management Studio or other tools. If your table contains 40 million rows of data and each row of data is on average 1000 bytes per row, opening this table might take 40 GB of memory to open and display the data. Be careful about viewing all rows of large tables.

Dave

|||

Cheers Dave,

I have used Management Studio, but you are probably right about the memory requirement.

Thanks

Other Generate Script problems in SQL Server Management Studio

Why have often used scripting settings been removed from SQL Server 2005:

Issues associated with Scripting options:

Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent, [] or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.

As

someone else said in another post - why have Microsoft removed useful functionality

from the scripting options. It doesn't make sense - were these options

over looked or was it a conscious decision.

When will these

settings be reinstated - (and they need to be able to be set for the

whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options

)

3rd Party Scripter for me until this is fixed - damn just need to find one now!

Alex

Hi Alex,

Some of these you will see addressed in SP2 -- Such as Create Drop options.

The others are in backlog of work items for the next general release of SQL Server.

A partial - (as in brief) explanation for why some disappeared had to do with 'forward' compatibility.

I will log your input in our Team Server database if you don't mind.

Thanks,

Terrence Nevins

SQL Server Program Manager

Other Generate Script problems in SQL Server Management Studio

Why have often used scripting settings been removed from SQL Server 2005:

Issues associated with Scripting options:

Query Analyzer allowed the following generic options that have been removed from SSMS:
Identifier Delimiter : None, Version dependent, [] or ""
Do not script the collation.
Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.
Do not include 'ON <filegroup>' clause directing filegroup use.
and table Scripting Options:
Qualify object name by its owner.
Do not include definition of identity property, seed, and increment.
Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Also Enterprise Manager Generate SQL Scripts tool had:
Files to Generate: Create one file or one file per object - Gone!
Create Drop object options - now it's one or the other.

As someone else said in another post - why have Microsoft removed useful functionality from the scripting options. It doesn't make sense - were these options over looked or was it a conscious decision.

When will these settings be reinstated - (and they need to be able to be set for the whole IDE as well as in the wizard (as per Query Analyzer:Tools|Options )

3rd Party Scripter for me until this is fixed - damn just need to find one now!

Alex

Hi Alex,

Some of these you will see addressed in SP2 -- Such as Create Drop options.

The others are in backlog of work items for the next general release of SQL Server.

A partial - (as in brief) explanation for why some disappeared had to do with 'forward' compatibility.

I will log your input in our Team Server database if you don't mind.

Thanks,

Terrence Nevins

SQL Server Program Manager

osql

i'm trying to execute some scripts created by the express studio script wizard. i can connect with the studio, the website (asp worker) but i can't create the right cmdline for osql ..... this is my osql line ......

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>osql -S (local)\\SQLEXPRESS -U sa -P sablah -i run.sql.......

this is the error i'm getting in my logs

Error: 18456, Severity: 14, State: 16.
2006-12-22 15:30:45.11 Logon Login failed for user 'sa'. [CLIENT: <local machine>]

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

First, you only use EITHER username/password OR Trusted_Connection -NOT both. So the second connection string should fail.

Second, in the connection string, the server 'should' have only one [\] between the machinename and the instancename.

|||Yah, i believe i posted that. for asp.net the string actually works. i know why its not supposed to be there. and thats obivously why i can't add that argument to the osql statement. but i'm still left with the original posting of my error. i'm not missing anything in my osql statement?|||

Reading your post again, it seems that you indicated that your ASP connection string failed.

is98 wrote:

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

ASP may be able to handle the two backslashes in the servername, but OSQL cannot. And if I recall, doesn't ASP use (localhost) instead of (local)?

Try: "server=(local)\SQLExpress" INSTEAD OF "Server=(local)\\SQLEXPRESS"

|||who the man? you the man. the localhost part works still ok. i just took out the extra slash which obiviously now explains why it couldn't find the server. thanks muchly.|||

The double back-slash is an escape character for C based languages, such as C#. When ever you want to pass a backslash in a C based language, you either need to use the "\\" escape sequence or use the @. to indicate a litteral string. You are passing literal strings to oSQL, not C#, so the "\\" is not needed, as you've found.

Just thought you'd be interested in understanding that this wasn't just random behaivor.

Mike