Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Posts
    23

    Unanswered: 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!

  2. #2
    Join Date
    Oct 2002
    Posts
    369
    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')

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    That keyword is not valid. If you include the table id without the index id - all indexes will be processed.

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Question

    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')

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  6. #6
    Join Date
    Oct 2002
    Posts
    369
    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?

  7. #7
    Join Date
    Nov 2002
    Posts
    23
    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

  8. #8
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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
    Code:
    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.
    MCDBA

  9. #9
    Join Date
    Nov 2002
    Posts
    23
    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?

  10. #10
    Join Date
    Nov 2002
    Posts
    23

    is this all??

    The only difference between levels 70 and 80 is that several reserved keywords introduced in SQL Server 2000 are not supported in level 70.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    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

    ms article 2 - This one says version 6.5, but there is some important information in general and specifically to version 7.

  12. #12
    Join Date
    Nov 2002
    Posts
    23

    I was wrong!

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •