October 26, 2007 at 11:13 am
I have a job to automate reindexing running on a SQL 2000. The job calls a sp called 'sp_reindex' (very creative I know). The sp is as follows:
DECLARE @ObjectName varchar(255)
DECLARE ObjectNames CURSOR
FOR SELECT [name]
FROM sysobjects
WHERE Type = 'u'
order by name
OPEN ObjectNames
FETCH ObjectNames INTO @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ObjectName
exec('DBCC DBREINDEX (''dbo.' + @ObjectName + ''','''',90)')
FETCH ObjectNames INTO @ObjectName
END
CLOSE ObjectNames
DEALLOCATE ObjectNames
When the job executes, it completes the reindex on the first table in the cursor and fails on the second regardless of what that second table is. (I changed the order of the objects in the cursor to test this.)
error:
Executed as user: XXXX\XXUser. ...leted. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) ACCESSPROFPERM [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
If I run the sp in management studio it completes no problems. If I change the sp to print the dbcc reindex lines instead of exec them, the step completes when run from the job. If I put the code that is generated by printing the results of the stored proc in the job step, it works fine as well.
I made sure that all objects were owned by dbo. I am stuck and would appreciate any suggestions.
Thanks,
J
October 26, 2007 at 11:46 am
Hey Jared,
The message 2528 is what DBCC Reindex always outputs. I would recommend using try catch in your exec statement and in the catch block, insert the "select ERROR_MESSAGE()" into a table. Then look at the error after running the job. I know this is a bit of SQL programming, but when you have jobs like this, the real error message usually gets lost in all the print garbage that DBCC outputs. Here is some example code
[font="System"]BEGIN TRY
DBCC...
END TRY
BEGIN CATCH
INSERT INTO TABLE
SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE()
END CATCH[/font]
Then if you want to post your results, we can probably help you better.
Thanks,
Eric
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply