SQL Server Performance

Reindexing In SQL Server 2000

Discussion in 'Performance Tuning for DBAs' started by DigitalFool, Aug 12, 2008.

  1. DigitalFool New Member

    I was looking for an alternative solution to using the maintenance plans in SQL 2000 and found an article here on SSP that is perfect for my maintenance needs.

    http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p2.aspx
    It is very similar the BOL reference to DBCC SHOWCONTIG with a few needed tweaks. However, I'm having problems executing as is according to the article by Tom Pullen. I've seen him around on the forums, so maybe he'll dive in, but basically I'm getting an error try to create the SP.Msg 207, Level 16, State 3, Procedure sp_defragment_indexes, Line 121
    Invalid column name '''.
    Msg 207, Level 16, State 3, Procedure sp_defragment_indexes, Line 121
    Invalid column name '''.

    Here is the line in question:

    SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" +
    ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
    Unfortunately, I'm an involuntary DBA, so I don't have the sharpest syntax skills, but I played around with the concatenation to a point where I could execute the SP. Nonetheless, I failed, as the results of actually passing the parameter results in an error over and over (for each index falling within the parameter).Line 1: Incorrect syntax near '.'.
    Msg 170, Level 15, State 1, Line 1

    Could anyone guide me to what I'm missing? Thanks in advance!
    dF
  2. Adriaan New Member

    Those double-quote characters are just asking for problems ..
    The use of single quotes in dynamic SQL is an art in itself, but this should work regardlessly:
    SELECT @execstr =
    'DBCC DBREINDEX (''' + RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' + ', '
    + RTRIM(@indexname) + ') WITH NO_INFOMSGS'

Share This Page