HELP - Error Message when running DBCC Showcontig with TableResults

Has anyone encountered the following error message when
running DBCC Showcontig with tableresults?
[Microsoft][ODBC SQL Server Driver]Unknown token received
from SQL Server
I am doing a dynamic DBCC on every table in my DB to
perform some analysis of indexes, however when I run the
full DB, I get this error. I printed the table.index for
each index prior to running the dbcc and when I take the
table and index and run independantly in ISQL, it executes
fine, but gives me a message about:
'DBCC execution completed. If DBCC printed error messages,
contact your system administrator.'
Which is not viable since it is a text string and not the
parameters of the TABLERESULTS table.
Anyone have a workaround or know what I can do to fix this?I'd start checking the database using CHECKDB. If that comes out clean, I'd say you've hit a bug
(check KB, be current and if that doesn't help open case with MS PSS).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Chris" <chris@.da-krewe.com> wrote in message news:066a01c391af$9183bca0$a101280a@.phx.gbl...
> Has anyone encountered the following error message when
> running DBCC Showcontig with tableresults?
> [Microsoft][ODBC SQL Server Driver]Unknown token received
> from SQL Server
> I am doing a dynamic DBCC on every table in my DB to
> perform some analysis of indexes, however when I run the
> full DB, I get this error. I printed the table.index for
> each index prior to running the dbcc and when I take the
> table and index and run independantly in ISQL, it executes
> fine, but gives me a message about:
> 'DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.'
> Which is not viable since it is a text string and not the
> parameters of the TABLERESULTS table.
> Anyone have a workaround or know what I can do to fix this?

Help (DBCC SHOWCONTIG ( + @tablename + )

Hi everyone, I'm SO glad I found this site.
Ok, here's my problem...

I found this stored proc somewhere to help defrag some tables, well this one line is giving me the hardest time. First I was getting an error about using FAST ('FAST' is not a recognized option.), so I took that out.
Now I'm getting 'ALL_INDEXES' is not a recognized option.

Here's the line giving me the problems.
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS,ALL_INDEXES')

Any ideas?

Thank you!RE: Here's the line giving me the problems.
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS,ALL_INDEXES')

Note: Often the line where the error is 'reported' to be (in query analyzer, etc.) is not where it the issue actually is; frequently that is just where the query parser 'chokes' on it. The line you gave in isolation should work OK. Have you tried it seperatly to test it? For example try:

Use Pubs
GO

Declare @.tablename As NVarChar (256)
Select @.tablename = 'Authors'

Exec ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')|||That keyword is not valid. If you include the table id without the index id - all indexes will be processed.|||RE: That keyword is not valid. If you include the table id without the index id - all indexes will be processed.

I'm not sure I understand what you mean, if you mean All_Indexes; as far as I understand it will cause results for "all indexes" to be displayed. In this case for Pubs..authors?

Use Pubs
GO

Declare @.tablename As NVarChar (256)
Select @.tablename = 'Authors'

Exec ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')|||I needed to be more specific - ALL_INDEXES is not a valid keyword for sql server 7 - that is why criki is having the problem. Same reason why fast was not working.|||Originally posted by rnealejr
I needed to be more specific - ALL_INDEXES is not a valid keyword for sql server 7 - that is why criki is having the problem. Same reason why fast was not working.

I had assumed criki was on Sql Server 2k; also I thought Sql 2k returns esults for a clustered / pk index (if you leave out index ids...) I guess 7.0 returns results for all inexes if you leave out index ids?|||Whoa! You guys lost me.
I am on 2000. I found the code in help. I would assume FAST and ALL_INDEXES would be accurate. I just figured there's some setting I'm missing.

The full code is in SQL Server Books Online under DBCC SHOWCONTIG|||You say you are running SQL Server 2000, was this an install or an upgrade from SQL Server 7.0. If it was an upgrade your Compatibility Level for the database may still be set to SQL Server 7.

Run this to see the levels
select cmptlevel,name from master.dbo.sysdatabases
If a database comes back with a cmptlevel not equal to 80, then it is not at 2000 level.

You can set a databases Compatibility Level by running sp_dbcmptlevel. However this may have some effect on your stored procedures, so test.|||They're all 70. :-(
Most of my databases don't have stored procedures, luckily in this case.

What type of issues and is there anyway to determine this before running running sp_dbcmptlevel?|||The only difference between levels 70 and 80 is that several reserved keywords introduced in SQL Server 2000 are not supported in level 70.|||When you upgrade from 7 to 2000 the default compatibility is to 2000 (8) so I am surprised that your compatibility it still for 7. Anyway, the short answer to your question is yes. Check out the following ms articles:

ms article 1 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_02wj.asp)

ms article 2 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_5dx1.asp) - This one says version 6.5, but there is some important information in general and specifically to version 7.|||So sorry! I was told Sql 2K but it is in fact 7.0. (that's what I get for not checking myself)

Now, I guess, best thing to do is just rewrite the proc to include the correct fields in the table creation and insert.

I'll have to start a new thread!

Thanks!