January 4, 2013 at 1:24 pm
I am using an inherited stored procedure created by a previous DBA to Update Statistics after running dbcc checkdb. (weekly maintenance job on SQL 2008 R2).
Error:
Cannot find a table or object with the name "[###].[#####]". Check the system catalog. [SQLSTATE 42S02] (Error 2501). The step failed.
Output in sp_dba_updatestats_errors.txt file:
Error number 2501 with the following SQL: DBCC UPDATEUSAGE ('dbname','.') WITH NO_INFOMSGS, COUNT_ROWS
The db in question does have many tables owned by dbo as well as other schemas which might be the problem within this code. Hoping for feedback from the SQL community to assist with a potential resolution.
Stored proc:
CREATE PROCEDURE [dbo].[sp_dba_updatestats]
@dbNameIN varchar(500) = NULL
AS
set nocount on
-- ---------------------------------------------------
-- Declare all the variables to be used in this script
-- ---------------------------------------------------
declare@dbNamevarchar(500), -- DB name list from the server
@tableNamevarchar(500), -- Table name list from each database in @dbName
@userNamevarchar(255), -- User name list from each table in @tableName
@tableListSQLvarchar(500), -- SQL used to populate @tableName and @indexName
@fooSQLvarchar(500), -- Reusable variable for holding misc SQL to execute
@errCheckchar(1), -- Used to turn error checking on (Y) and off (N)
@errFilevarchar(255), -- Location of error file (ensure validity per server)
@errint, -- Reusable variable for holding @@error results
@errStringvarchar(255), -- Reusable variable to create error message syntax
@fooCmdvarchar(500), -- Reusable variable for holding misc OS commands
@errCounterint -- Incrementing variable to keep track of errors
-----------------------------------------------------
-- Check for job output dir. If not found create it.
-----------------------------------------------------
DECLARE @result int
EXEC @result = xp_cmdshell 'dir d:\Job_Output\*.*'
IF (@result > 0)
EXEC xp_cmdshell 'mkdir d:\Job_Output'
set @dbNameIN = NULL -- Default variable to NULL
set @errCheck = 'Y' -- Usually should be set to 'Y'
set @errFile = 'd:\job_output\sp_dba_updatestats_errors.txt'
set @errCounter = 0 -- Reset to 0
set @err = 0 -- Reset to 0
set @fooCmd = 'del ' + @errFile -- OS command to remove previous @errFile
exec master..xp_cmdshell @fooCmd -- SP to remove previous @errFile
select 'START TIME ',getdate()
-- --------------------------------
-- Create all the temp tables first
-- --------------------------------
create table #databaselist (dbname char(300))
create table #tablelist (tablename varchar(500), username varchar(255))
-- --------------------------------------------------------------------
-- Get all the database names into a table if dbNameIN variable is NULL
-- --------------------------------------------------------------------
IF @dbNameIN IS NULL
BEGIN
insert into #databaselist
select name from master.sys.databases
where state_desc = 'ONLINE' and
name not in ('master','model','msdb','Northwind','AdventureWorks','pubs','tempdb')
END
-- ------------------------------------------------------------------
-- Declare the cursor and populate with the contents of #databaselist
-- ------------------------------------------------------------------
IF @dbNameIN IS NULL
declare dbCursor cursor for select RTRIM(dbname) from #databaselist
ELSE
declare dbCursor cursor for select RTRIM(@dbNameIN)
-- ---------------------------------------------------------
-- Open a cursor to process all user databases on the server
-- ---------------------------------------------------------
open dbCursor
-- --------------------------------------------------------
-- Populate variable with the initial value from the cursor
-- --------------------------------------------------------
fetch next from dbCursor into @dbName
while (@@fetch_status = 0)
begin
-- ----------------------------------------------------------------------
-- SQL select the tableName and indexName for each database in the cursor
-- ----------------------------------------------------------------------
set @tableListSQL = 'use [' + @dbName + '] select name, user_name(uid) from sysobjects where type = ''U'' and name != ''MSlast_job_info'''
-- --------------------------------------------------------------
-- Truncate #tablelist before populating with new database tables
-- --------------------------------------------------------------
truncate table #tablelist
-- ----------------------------------------------------
-- Populate #tablelist with the output of @tableListSQL
-- ----------------------------------------------------
insert into #tablelist
exec(@tableListSQL)
-- -----------------------------------------------------------
-- Declare cursor and populate with the contents of #tablelist
-- -----------------------------------------------------------
declare tableCursor cursor for select tablename, username from #tablelist
-- -------------------------------------------------------------------------
-- Open the cursor and process for each table in the current database cursor
-- -------------------------------------------------------------------------
open tableCursor
-- --------------------------------------------------------
-- Populate variable with the initial value from the cursor
-- --------------------------------------------------------
fetch next from tableCursor into @tableName, @userName
while (@@fetch_status = 0)
begin
/* ---------------- Start Section to UPDATE USAGE stats --------------------- */
-- ---------------------------------------------------------------------
-- Create the SQL statement to perform the DBCC UPDATEUSAGE, execute and
-- capture the return code (used to correct sysindexes entries)
-- ---------------------------------------------------------------------
set @fooSQL = 'DBCC UPDATEUSAGE (''' + @dbName + ''',''[' + @userName + '].[' + @tableName + ']'') WITH NO_INFOMSGS, COUNT_ROWS'
exec (@fooSQL)
set @err = @@error
-- ------------------------------
-- If error checking is turned on
-- ------------------------------
if @errCheck = 'Y'
begin
-- --------------------------------------------------
-- Create the error message to be written to @errFile
-- --------------------------------------------------
set @errString = 'Error number ' + ltrim(str(@err)) + ' with the following SQL: ' + @fooSQL
-- --------------------------------------------
-- If there actually IS an error then handle it
-- --------------------------------------------
if @err<>0
begin
-- ---------------------
-- Increment @errCounter
-- ---------------------
set @errCounter = @errCounter + 1
-- -------------------------------------------------------
-- If @errCounter = 1 we want to create a @errFile, create
-- the string and write it out to @errFile
-- -------------------------------------------------------
if @errCounter = 1
begin
set @fooCmd = 'echo ' + @errString + ' > ' + @errFile
exec master..xp_cmdshell @fooCmd
end
-- -------------------------------------------------------
-- Else, if @errCounter > 1 we want to append to @errFile,
-- create the string and write it out to @errFile
-- -------------------------------------------------------
else
begin
set @fooCmd = 'echo ' + @errString + ' >> ' + @errFile
exec master..xp_cmdshell @fooCmd
end
end
end
/* ----------------- End Section to UPDATE USAGE stats ---------------------- */
/* ------------------- Start Section to UPDATE STATS ------------------------ */
-- ----------------------------------------------------------------------
-- Create the SQL statement to perform the UPDATE STATISTICS, execute and
-- capture the return code
-- ----------------------------------------------------------------------
set @fooSQL = 'UPDATE STATISTICS [' + RTRIM(@dbName) + '].[' + RTRIM(@userName) + '].[' + RTRIM(@tableName) + '] WITH FULLSCAN'
exec (@fooSQL)
set @err = @@error
-- ------------------------------
-- If error checking is turned on
-- ------------------------------
if @errCheck = 'Y'
begin
-- --------------------------------------------------
-- Create the error message to be written to @errFile
-- --------------------------------------------------
set @errString = 'Error number ' + ltrim(str(@err)) + ' with the following SQL: ' + @fooSQL
-- --------------------------------------------
-- If there actually IS an error then handle it
-- --------------------------------------------
if @err<>0
begin
-- ---------------------
-- Increment @errCounter
-- ---------------------
set @errCounter = @errCounter + 1
-- -------------------------------------------------------
-- If @errCounter = 1 we want to create a @errFile, create
-- the string and write it out to @errFile
-- -------------------------------------------------------
if @errCounter = 1
begin
set @fooCmd = 'echo ' + @errString + ' > ' + @errFile
exec master..xp_cmdshell @fooCmd
end
-- -------------------------------------------------------
-- Else, if @errCounter > 1 we want to append to @errFile,
-- create the string and write it out to @errFile
-- -------------------------------------------------------
else
begin
set @fooCmd = 'echo ' + @errString + ' >> ' + @errFile
exec master..xp_cmdshell @fooCmd
end
end
end
/* -------------------- End Section to UPDATE STATS ------------------------- */
-- -------------------------------------------------------------
-- Get the next value in the cursor, if exhausted then exit loop
-- -------------------------------------------------------------
fetch next from tableCursor into @tableName,@userName
end
-- ----------------------------------------------
-- Properly close and deallocate the cursors used
-- ----------------------------------------------
close tableCursor
deallocate tableCursor
-- --------------------------------------------------------------
-- Get the next value in the cursor, if exhausted, then exit loop
-- --------------------------------------------------------------
fetch next from dbCursor into @dbName
end
-- ----------------------------------------------
-- Properly close and deallocate the cursors used
-- ----------------------------------------------
close dbCursor
deallocate dbCursor
-- ---------------------------------------------
-- drop the temporary tables used for processing
-- ---------------------------------------------
drop table #databaselist
drop table #tablelist
set nocount off
select 'END TIME ',getdate()
January 4, 2013 at 2:00 pm
the script was originally written for SLq2000;
it's using the old sysobjects instead of sys.tables
now, objects are by schema, not by userr;
so the offending code is here:
set @tableListSQL = 'use [' + @dbName + '] select name, user_name(uid)
from sysobjects
where type = ''U''
and name != ''MSlast_job_info'''
it could probably be replaced with this:
set @tableListSQL = 'use [' + @dbName + '] select name,schema_name(schema_id)
from sys.tables
where name != ''MSlast_job_info'''
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply