How to get rid f this error, please help?

[jsql12]

When I run the following:


SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @TableName VARCHAR (255)
,@SQLStr VARCHAR (255)
,@IndexName VARCHAR (255)
,@MaxLogicalFrag DECIMAL
,@MaxScanDensity DECIMAL

SET @MaxLogicalFrag = 0 --0 is the best
SET @MaxScanDensity = 100 --100 is the best

--INSERT EACH TABLE'S DBCC SHOWCONTIG RESULTS INTO A TABLE
DECLARE TABLELIST CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

CREATE TABLE #DBCCContigList (
ObjectName CHAR(255)
,ObjectId INT
,IndexName CHAR(255)
,IndexId INT
,Lvl INT
,CountPages INT
,CountRows INT
,MinRecSize INT
,MaxRecSize INT
,AvgRecSize INT
,ForRecCount INT
,Extents INT
,ExtentSwitches INT
,AvgFreeBytes INT
,AvgPageDensity INT
,ScanDensity DECIMAL
,BestCount INT
,ActualCount INT
,LogicalFrag DECIMAL
,ExtentFrag DECIMAL
)

OPEN TABLELIST

FETCH NEXT FROM TABLELIST INTO @TableName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
INSERT INTO #DBCCContigList
EXEC ('DBCC SHOWCONTIG (''' + @TableName + ''') WITH FAST,TABLERESULTS,ALL_INDEXES,NO_INFOMSGS')
FETCH NEXT FROM TABLELIST INTO @TableName
END

CLOSE TABLELIST
DEALLOCATE TABLELIST
-------------------------------------------------------------------------------
--USING THE PREVIOUS TEMP TABLE, CURSOR THROUGH EACH TABLE'S INDEX
--AND PERFORM A DBCC REINDEX
DECLARE INDEXLIST CURSOR FOR
SELECT ObjectName
,IndexName
FROM #DBCCContigList
WHERE LogicalFrag > @MaxLogicalFrag
OR ScanDensity < @MaxScanDensity
--CAN'T REINDEX HEAP(0) AND IMAGE/TEXT(255)
AND INDEXID NOT IN (0,255)

OPEN INDEXLIST

FETCH NEXT FROM INDEXLIST INTO @TableName,@IndexName

WHILE (@@FETCH_STATUS <> -1)
BEGIN
--PRINT 'Executing DBCC DBREINDEX ' + '(' + '[' + RTRIM(@TableName) + ']' + ',' + '['+ RTRIM(@IndexName) + ']' + ',90)'

SET @SQLStr = 'DBCC DBREINDEX ' + '(' + '[' + RTRIM(@TableName) + ']' + ',' + '[' + RTRIM(@IndexName) + ']' + ',90) WITH NO_INFOMSGS'

EXEC (@SQLStr)
--CHECKPOINT IS THE KEY TO THIS SCRIPT SO THE TLOG DOESN'T GROW OUT OF CONTROL.
--WHEN DB IS IN SIMPLE RECOVERY MODE, THEN IT INITIATES 'TRUNCATING' OF THE TLOG.
CHECKPOINT

FETCH NEXT FROM INDEXLIST INTO @TableName,@IndexName
END

CLOSE INDEXLIST
DEALLOCATE INDEXLIST

DROP TABLE #DBCCContigList
GO

I get this error:
Server: MSG 1038, level 15, state3, line 1
cannnot use empty object or column names. Use a single space if necessary.

My question is how to get rid of the error message? The query still runs in query analyzer but not from inside of a scheduled job.
Any help would be greatly appreciated.

Thanks!

 

[monksnake]

Uncle Rico, is this you???

<.

 

[SQLDenis]

you have a column that which has a name which is a space
when you trim it it doesn't match

instead of this
'[' + RTRIM(@TableName) + ']

do this

QUOTENAME(@TableName)

example

select quotename(' ')

output
[ ]




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jsql12]

Thanks for your fast response. But, I still get the same error.

 

[gmmastros]

Try changing:

,@SQLStr VARCHAR (255)


To

[tt][blue] ,@SQLStr VARCHAR ([!]8000[/!])[/blue][/tt]




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

run this

SELECT ObjectName
,IndexName
FROM #DBCCContigList

and look what comes up blank

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[jsql12]

Thanks again!
the IndexName column has blank space in it.

 

[jsql12]

Thank you all very much! especially, SQLDennis
I think I found out where my problem was: I didn't enclose my OR comparison between brackets.

so one of the statements should have been like this:

-------------------------------------------------------------------------------
--USING THE PREVIOUS TEMP TABLE, CURSOR THROUGH EACH TABLE'S INDEX
--AND PERFORM A DBCC REINDEX
DECLARE INDEXLIST CURSOR FOR
SELECT ObjectName
,IndexName
FROM #DBCCContigList
--->WHERE (LogicalFrag > @MaxLogicalFrag
OR ScanDensity < @MaxScanDensity)<------
--CAN'T REINDEX HEAP(0) AND IMAGE/TEXT(255)
AND INDEXID NOT IN (0,255)

This code is actually great for DBCC DBREINDEX of largely fragmented data pages, it a time saver you guys can use it if you want to.

Thanks again!