SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 defrag index DTS failed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/24/2006 :  11:13:06  Show Profile  Reply with Quote
Hi!

I have a weekly job that has been working for over half a year, but it has failed for last couple weeks.

The DTS job is about defragmenting indexes for a db. It calls a custom sp which is from http://www.sql-server-performance.com/tp_automatic_reindexing.asp.

The history said: DBCC failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’, ‘ARITHABORT’.

Both of them are OFF when I used sp_dboption.

Toward the end of the sp, it has this lines:

SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Now executing: '
SELECT(@execstr)
EXEC (@execstr)

SET QUOTED_IDENTIFIER OFF

And there is no ARITHABORT to be found in the sp.

What might went wrong?

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/24/2006 :  12:44:03  Show Profile  Reply with Quote
More than likely, it is because you recently added a computed column to a table.





CODO ERGO SUM
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/24/2006 :  14:07:37  Show Profile  Reply with Quote
Thank you for the reply!

Ok, I guess adding a default function to a datetime field will be one of the "computed column". I will search for any other changes, meanwhile, how does this change conflicts with those options?

I will remove the default and try to run the DTS again. And if it is the reason as you have suggested, how can I make them co-exist? Do I have to give up one for the other?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/24/2006 :  14:36:06  Show Profile  Reply with Quote
A default is not a computed column. You should be able to identify the table with the computed column by looking at the table where the error occurs.

If you don't know what a computed column is, read about it in SQL Server Books Online.

All you really have to do is change the settings of QUOTED_IDENTIFIER and ARITHABORT with the SET command before you run the DBCC REINDEX.

CODO ERGO SUM
Go to Top of Page

Hommer
Aged Yak Warrior

784 Posts

Posted - 04/24/2006 :  14:53:02  Show Profile  Reply with Quote
I went through error message on the DTS history, and did not find any thing specific to a table. Ironically, I am the primary db developer, and if I don't know what computed columns are, then most likely nobody would have added one into the table.

Also, the sp has Set QUOTED_IDENTIFIER ON before the DBCC REINDEX. I will add Set ARITHABORT ON and give that a try.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000