Error Missing Column Statistics

Laura

unread,

Jun 13, 2002, 11:08:38 AM6/13/02

to

Getting the following error on an application database in
the system tables. I cannot find any documentation on what
I should do.
Any help would be appreciated.

This is the statement that runs before the error.

-SQL:BatchStarting
select c.name, c.status, o.name from syscolumns c,
sysobjects o where c.id = object_id ('SYSDBA.SECRIGHTS')
and c.cdefault *= o.id order by colid ASC

This is the ERROR I get

-Missing Column Statistics NO STATS:([c].[cdefault])
NO STATS:([c].[cdefault])

Eric Weinraub[MS]

unread,

Jun 13, 2002, 3:03:32 PM6/13/02

to

What release of SQL server is this? From what client and version is this
query being run from? Sounds like an upgrade issue.

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

alfred

unread,

Jun 13, 2002, 3:13:54 PM6/13/02

to

If it is SQl 7.0 or above , try running EXEC
sp_createstats on a test environment first. Just a
suggestion !

>.
>

Laura

unread,

Jun 13, 2002, 3:36:49 PM6/13/02

to

Thanks for your reply

I created a test db and tried the EXEC sp_createstats
I get this message.

Server: Msg 515, Level 16, State 2, Procedure
sp_createstats, Line 0
Cannot insert the value NULL into column 'col_name',
table 'tempdb.dbo.#colpostab__000100000500'; column does
not allow nulls. INSERT fails.
The statement has been terminated.

Do you know what this means ?

>.
>

Laura

unread,

Jun 13, 2002, 3:30:37 PM6/13/02

to

Thanks for your reply

Were running SQL 7.0 spk 3 on NT 4 Spk 6a. The client is a
product called SalesLogix connected with TCP/IP in the
client utility.

>.
>

[MS] Eric Yang

unread,

Jun 17, 2002, 3:45:28 AM6/17/02

to

Hello,

The 'Missing Column Statistics'message indicates that statistical
information the Optimizer could have used to generate a better query plan
was unavailable or the statistics are out of date. This indicates that the
query might not have useful indexes on at least one table involved. Beyond
not having a useful index, SQL Server might not even have statistical data
about the column(s) involved to make an informed decision for a query plan.

Please enable the 'master' database Options 'AutoCreate Stats' and
'AutoUpdate Stats'.
Then run the following command in the query analyzer.

Exec sp_createstats 'syscolumns.cdefault'

Sincerely,

Eric Yang [MS]
Microsoft Online Support Engineer

Please do not send email directly to this alias. This is our online
account name for newsgroup participation only.

This posting is provided 'AS IS' with no warranties, and confers no rights.

Get Secure! - www.microsoft.com/security

Russell Scherer

unread,

Jul 8, 2002, 6:26:22 PM7/8/02

to

Hello Laura,

Are you seeing this in a SQL Server profiler trace? I see the exact same
thing as you do in a trace but I would not worry about it since it really
isn't an error. SQL Server is merely warning you that it may have been able
to make use of statistics on this particular column (i.e.,cdefault) but
there were none available. If you try and drop statistics on this table in
Query Analyzer as such:

DROP STATISTICS syscolumns.cdefault

You should see the following error:

Server: Msg 3701, Level 11, State 7, Line 1
Cannot drop the statistics 'syscolumns.cdefault', because it does not exist
in the system catalog.

since no statistics exist on it, but that's the reason why you get this
warning.

In general, if you receive this warning on a system table like syscolumns,
I would simply ignore it. However, if you see this problem on your user
tables, then you may want to ensure that auto create and auto update
statistics are turned on, or consider performing an index analysis on the
table involved.

Regards,
Russ

This posting is provided “AS IS” with no warranties, and confers no rights.