tsql- set options

Question

I am rebuilding indexes using a script which reorganises or rebuilds indexes according to a predefined fill factor.

It is on SQl 2000.

I have the following SET options in my script:

SET ANSI_WARNINGS OFF
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

I am getting the following error:

DBCC failed because the following SET options have incorrect settings: 'ANSI_WARNINGS, ARITHABORT'

So, is it telling me that i should turn ANSI_WARNINGS OFF AND INCLUDE ARTHABORT TO ON?

I am confused with this. Any advice?

Answer 1

I think you want to have:

SET ANSI_WARNINGS ON;
SET ARITHABORT ON;

You can see more detail about this here:

http://support.microsoft.com/kb/301292

When you move to SQL Server 2005+ you should use ALTER INDEX REBUILD/REORGANIZE as opposed to DBCC commands. Also, strongly suggest you check out SQLFool's and Ola's options, they can make your reindexing and rebuilding jobs much easier: http://sqlfool.com/2009/06/index-defrag-script-v30/ and http://ola.hallengren.com/ I don't remember if either of these will work on 2000 but worth checking out.

Answer 2

From SET ANSI_NULLS for SQL Server 2000 (however, same applies to later versions):

Note: ANSI_NULLS is one of seven SET options that must be set to required values when dealing with indexes on computed columns or indexed views. The options ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, and CONCAT_NULL_YIELDS_NULL also must be set to ON, while NUMERIC_ROUNDABORT must be set to OFF.

So, both should be ON