ANSI_NULLS error when reindexing

[lory]

I have a reindex script that is failing with the error "Msg 1934, Sev 16: DBCC failed because the following SET options have incorrect settings: 'ANSI_NULLS.'. [SQLSTATE 42000]"

I ran SET ANSI_NULLS ON, then ran the reindex script and it failed again with the same error? It doesn't appear that the ANSI_NULLS is turning on?

I'm no pro at this level, so any advice would be appreciated. Thanks.

 

[JayKusch]

please post your script

Thanks

J. Kusch

 

[lory]

Here's the script:

SET ANSI_NULLS ON
GO
spU_reindex_tables_new
SET ANSI_NULLS OFF
GO

And here is the stored procedure:

DECLARE x SCROLL CURSOR
FOR SELECT DISTINCT o.name
FROM
sysobjects o, sysindexes i
where
o.type = 'U'
and i.id=o.id
and i.id>98
and i.name not like '_hind%'
and i.name not like '_WA%'
and o.name not like 'dt%'
and o.name not like 'PSAPMS%'
and i.indid != 0
and o.name <> 'PS_PO_PRINT_COM_WS'
and o.name <> 'PS_QS_ALRMPORT_TBL'
and o.name <> 'PS_VCHR_ACCTG_LINE'
and o.name <> 'PS_TRANSACTION_INV'
and o.name <> 'PS_COST_INV'
and o.name <> 'PS_CM_ACCTG_LINE'
and o.name <> 'PS_DISTRIB_LINE'

order by o.name

/* OPTIONAL WHERE CLAUSE */

OPEN x

SET NOCOUNT ON
DECLARE @id char(30)

DECLARE @output char(255)
DECLARE @msg char(255)
FETCH FIRST FROM x INTO @id

WHILE @@fetch_status <> -1
BEGIN
SELECT @output = 'DBCC DBREINDEX (' + RTRIM(@id) + ')'
SELECT @msg = 'REINDEXING ' + RTRIM(@id)
PRINT @msg
EXEC (@output)

FETCH NEXT FROM x INTO @id
END
CLOSE x
DEALLOCATE x

DBCC UPDATEUSAGE(0) WITH COUNT_ROWS
GO

 

[JohnDTampaBay]

Perhaps this will help. From BOL:

SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.