CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2000
Manjot
Manjot

Reputation: 11516

tsql- set options

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?

Upvotes: 0

Views: 682

Answers (2)

gbn
gbn

Reputation: 432361

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

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280413

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.

Upvotes: 2

Related Questions