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.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2006-04-24 : 11:13:06
|
| 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 ONSELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'SELECT 'Now executing: 'SELECT(@execstr)EXEC (@execstr)SET QUOTED_IDENTIFIER OFFAnd there is no ARITHABORT to be found in the sp. What might went wrong? Thanks! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-24 : 12:44:03
|
| More than likely, it is because you recently added a computed column to a table.CODO ERGO SUM |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-04-24 : 14:07:37
|
| 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? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-24 : 14:36:06
|
| 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 |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2006-04-24 : 14:53:02
|
| 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. |
|
|
|
|
|
|
|
|
defrag index DTS failed