SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

Question

Post reply

SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

Bhuvnesh

SSC Guru

Points: 59351
More actions
October 10, 2008 at 3:19 am

#92649

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SYNC_OBJECT_SEND](
[object_text] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[object_name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[eventtype] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Context_Info] [varbinary](max) NOT NULL,
CONSTRAINT [PK_object_name_SYNC_OBJECT_SEND] PRIMARY KEY CLUSTERED
(
[object_name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------------
--Step 2
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER on
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE name = N'DDL_tr_DOMAIN_DBA_SyncUp_SP' AND parent_class=0)
DROP TRIGGER [DDL_tr_CVENT_DBA_SyncUp_SP] ON DATABASE
go
CREATE TRIGGER [DDL_tr_CVENT_DBA_SyncUp_SP]
ON DATABASE
FOR CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
AS
IF @@SERVERNAME <> 'INTECHDBA' RETURN
DECLARE @data XML
DECLARE @commandtext NVARCHAR(MAX),@object_name NVARCHAR(MAX),@EventType NVARCHAR(MAX)
DECLARE @SQlCmd NVARCHAR(MAX)
SET @data = EVENTDATA();
SET @commandtext = rtrim(ltrim( @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)')))
SET @object_name = rtrim(ltrim( @data.value('(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(MAX)')))
SET @EventType = rtrim(ltrim( @data.value('(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(MAX)')))
SET @commandtext= REPLACE(@commandtext ,'''','''''')
SET @object_name= REPLACE(@object_name ,'''','''''')
SET @EventType = REPLACE(@EventType ,'''','''''')
--SET @SQlCmd = 'INSERT INTO CVENT_DBA.dbo.SYNC_OBJECT_SEND(object_text,object_name,EventType) ' +
--' VALUES (''' + @commandtext + ''',''' + @object_name + ''',''' + @EventType + ''' )'
SET @SQlCmd = 'INSERT INTO DBA.dbo.SYNC_OBJECT_SEND(object_text,object_name,EventType,Context_Info) ' +
' VALUES (''' + @commandtext + ''',''' + @object_name + ''',''' + @EventType + ''',''' + CAST(Context_Info() as varchar(7)) + ''' )'
PRINT @SQlCmd
EXEC (@SQlCmd)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [DDL_tr_CVENT_DBA_SyncUp_SP] ON DATABASE
-----------------------------------------------
--Step 3
create proc sp_test
as
select getdate()
--------------------------------------------------------------
--Step 4
alter proc sp_test
as
select 'bhuv'
--------------------------------------------------------------
when i above script it gives following error
----------------------------------------------------------------------------------------
Msg 1934, Level 16, State 1, Procedure DDL_tr_CVENT_DBA_SyncUp_SP, Line 12
SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
-----------------------------------------------------------------------
PLease help 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

SET ANSI_NULLS and QUOTED_IDENTIFIER to ON before the create statement and see if that fixes the problem.

.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

before which create statement i need to do it ??

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

You only have two CREATE statements in your script and the first CREATE statement already has it.

Now find out the missing place 🙂

.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

thanks for reply but

its giving same error with below script

------------------------------------------------------

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER on

GO

create proc sp_test1

as

select getdate()

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

can anybody help me ??

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

can anybody help me ??

i got stuck BADLY 🙁

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

jacob sebastian SSChampion Points: 11822 More actions · Answer 7

Try executing the scripts for each object one by one and this might help you to identify the statement that fails.

.