August 6, 2006 at 10:10 pm
I am running the following script for re-building the indexes. Getting this error at the end,
Updating BOOKING_REPORTS_DM_TEMP_03
Server: Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'PSM_BOOKING_REPORTS_DM_temp_03'. Check sysobjects.
I have checked sysobjects, which has entry for this particular table.
Could anyone tell what could be the reason for this error?
/*
Function: For each user database rebuild indexes, update statistics and shrink.
Instructions: Run against master, the script will use the system catalog to produce a list of databases
If you want to excluce a database add the excluded db name to the 'not in' list below.
You can also adjust the target fillfactor below.
*/
Set quoted_identifier off
use master
go
DECLARE @fillfactor varchar(2)
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE @dataname varchar(30)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name in ('PSM_DM')
/* Variable Initialization */
select @fillfactor = "0" -- Set Fill factor here
-- Note "0" will use original fillfactor.
/* End Variable Initialization */
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
PRINT " "
PRINT @dataname_header
PRINT " "
EXEC ("USE " + @dataname + " DECLARE tnames_cursor CURSOR FOR SELECT name from sysobjects where type = 'U'")
Select @dataname_header = RTRIM(UPPER(@dataname))
Exec ("Use " + @dataname)
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM tnames_cursor INTO @tablename
CONTINUE
END
SELECT @tablename_header = " Updating " + RTRIM(UPPER(@tablename))
PRINT ""
PRINT @tablename_header
EXEC ("USE " + @dataname + " DBCC DBREINDEX (" + @tablename + "," + "''" + "," + @fillfactor + ")")
EXEC ("USE " + @dataname + " UPDATE STATISTICS " + @tablename)
FETCH NEXT FROM tnames_cursor INTO @tablename
END
DEALLOCATE tnames_cursor
EXEC("DBCC SHRINKDATABASE (" + @dataname + ", TRUNCATEONLY)")
FETCH NEXT FROM datanames_cursor INTO @dataname
END
DEALLOCATE datanames_cursor
PRINT ""
PRINT " "
PRINT "Indexing and shrinkage complete for All User Databases"
August 6, 2006 at 10:28 pm
1. try
select * from [PSM_BOOKING_REPORTS_DM_temp_03]
2. I am suspecting space after/befor the table name.
3. Run DBCC CHECKTABLE (PSM_BOOKING_REPORTS_DM_temp_03) to check corruption in the table
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply