Failed Job Step - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Failed Job Step

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-18 : 20:55:07

quote:
[SQLSTATE 01000] (Message 2528) DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'. [SQLSTATE 42000] (Error 1934). The step failed.

I get this error running

DBCC REINDEX

As a scheduled job step. Same command runs fine in QA.

Ideas / Help ?

Sam

<cough><ahem!>

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-19 : 10:43:45

I thought you went skiing...

http://www.skiwhitetail.com/

Did it ever run scheduled?

Did you select the correct database in the job step?

Put it in a sproc and execut the sproc as a scheduled job and let us know...

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-19 : 10:53:57

Guilt, Ethics and job security got in the way of skiing.

I had it in a sproc, which ran fine in QA, but not scheduled as a job.

Now I'm executing SQL directly in the JOB, and I get a better error.

Someone else told me to delete and recreate. I'll do it tonight when usage is down.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-19 : 11:30:05

quote:
Originally posted by SamC

Now I'm executing SQL directly in the JOB, and I get a better error.

That's funny...you should have went skiing...

What's the new error?

And are you trying to rebuild all of the indexes, or just a couple of tables?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-19 : 11:54:00

The "better" error was the message I posted in the first post.

This step crashes on the first command. It's a string of Table Reindexes.

Like this:

DBCC DBREINDEX([dbo.AdminAssignments], '', 80)
DBCC DBREINDEX([dbo.AdminMenuItems], '', 80)
DBCC DBREINDEX([dbo.Admins], '', 80)
DBCC DBREINDEX([dbo.AdminSnippets], '', 80)
DBCC DBREINDEX([dbo.Assignments], '', 80)
DBCC DBREINDEX([dbo.BonusQuestions], '', 80)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-20 : 12:44:09

Sam, what service pack level is SQL Server running on? I have never seen these errors before, but perhaps a later service pack has addressed it.

SELECT @@VERSION

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-20 : 12:55:30

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON

Fixed the problem - at least for a smaller set of DBREINDEX . Tonight I'll see if it runs the whole buritto.

Sam

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-02-20 : 14:54:15

It should fix your issue Sam. The issue arises on two issues. I'm going to go out on a limb and guess you have computed columns? We had this issue arise here. Setting the arithabort and quoted_identifiers in the procedure fixed the issue for us just fine.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_system_admin_maintenance
--Name: sp_system_admin_maintenance
--Purpose: This stored Procedure(SP) will be used for running nightly maintenance on
-- all databases. It resides in the master database of each database server.
-- 1) DBCC DBREINDEX on all tables.
-- 2) UPDATE STATISTICS on all tables.
-- 3) Recompile all procedures.
-- 4) UPDATE USAGE on the database.
-- 5) DBCC CHECKDB on the database.
--
--Format: EXEC sp_system_admin_maintenance
--
--Example: EXEC sp_system_admin_maintenance
--
--Action: Author: Date: Comments:
--------- ------------- --/--/---- ----------------------------------------
--Created Derrick Leggett 11/11/2003 Initial Development
--
AS
SET ARITHABORT ON
--Record start time
PRINT 'START TIME: ' + CAST(GETDATE() AS VARCHAR(255))
--Declare needed variables.
DECLARE
@int_max INT,
@int_counter INT,
@txt_sql NVARCHAR(4000),
@txt_name NVARCHAR(4000),
@txt_owner NVARCHAR(255),
@txt_db NVARCHAR(255)
--Declare table variable to hold table or procedure names.
DECLARE @tbl_names TABLE (
int_id INT IDENTITY(1,1) PRIMARY KEY,
txt_name VARCHAR(255),
txt_owner VARCHAR(255))
--Insert into table all user tables.
INSERT @tbl_names(
txt_name,
txt_owner)
SELECT
so.name,
su.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = 'U'
AND so.name NOT LIKE 'dt%'
--Set up loop to run DBCC commands against all user tables.
SELECT
@int_max = (SELECT MAX(int_id) FROM @tbl_names),
@int_counter = (SELECT MIN(int_id) FROM @tbl_names)
WHILE @int_counter <= @int_max
BEGIN
SELECT @txt_name = (
SELECT '[' + txt_owner + '].[' + txt_name + ']'
FROM @tbl_names
WHERE int_id = @int_counter)
--Reindex all user tables.
SELECT @txt_sql = 'DBCC DBREINDEX(''' + @txt_name + ''')'
PRINT @txt_sql
EXEC ( @txt_sql )
--Update the statistics on all user tables.
SELECT @txt_sql = 'UPDATE STATISTICS ' + @txt_name
PRINT @txt_sql
EXEC ( @txt_sql )
SELECT @int_counter = @int_counter + 1
END
--Insert into table all procedures.
DELETE @tbl_names
INSERT @tbl_names(
txt_name,
txt_owner)
SELECT
so.name,
su.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = 'P'
--Set up loop to force a recompile of all stored procedures.
SELECT
@int_max = (SELECT MAX(int_id) FROM @tbl_names),
@int_counter = (SELECT MIN(int_id) FROM @tbl_names)
WHILE @int_counter <= @int_max
BEGIN
SELECT @txt_name = (
SELECT '[' + txt_owner + '].[' + txt_name + ']'
FROM @tbl_names
WHERE int_id = @int_counter)
--Recompile the procedures.
SELECT @txt_sql = 'EXEC sp_recompile ''' + @txt_name + ''''
PRINT @txt_sql
EXEC ( @txt_sql )
SELECT @int_counter = @int_counter + 1
END
SELECT @txt_db = DB_NAME()
--Update the usage on the database.
PRINT 'DBCC UPDATEUSAGE (' + @txt_db + ') WITH NO_INFOMSGS'
DBCC UPDATEUSAGE (@txt_db) WITH NO_INFOMSGS
--Update the usage on the database.
PRINT 'DBCC CHECKDB (' + @txt_db + ') WITH NO_INFOMSGS'
DBCC CHECKDB (@txt_db) WITH NO_INFOMSGS
--Record end time.
PRINT 'END TIME: ' + CAST(GETDATE() AS VARCHAR(255))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-20 : 15:03:04

A couple of tables do have computed columns.

Thanks,

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources