CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
Henrique Melicio
Henrique Melicio

Reputation: 79

SQL Server - Using Quoted Identifier ON and OFF and Getdate()

I have a problem with my script SQL, please help me.

Ex:

I have a insert statments:

INSERT INTO CUSTOMER (Code, Date) VALUES (1, GETDATE());

When I execute this insert, retuns the follow message:

"Msg 1934, Level 16, State 1, Server HENRIQUE-PC, Line 5 INSERT failed because the following SET options have incorrect settings: 'QUOTED _IDENTIFIER'. Verify that SET options are correct for use with indexed views and /or indexes on computed columns and/or filtered indexes and/or query notificatio ns and/or XML data type methods and/or spatial index operations.".

Now, when I used SET QUOTED_IDENTIFIER ON, my insert is executed with success.

Ex:

SET QUOTED_IDENTIFIER OFF
GO

INSERT INTO CUSTOMER (Code, Date) VALUES (1, GETDATE());

SET QUOTED_IDENTIFIER ON
GO

(1 row(s) affected)

What relationship betwhen GETDATE() and QUOTED IDENTIFIER?

Why I need to use QUOTED IDENTIFIER in this case?

I believe it is because of getdate. Why?

Thanks.

Henrique Melicio

Upvotes: 4

Views: 5068

Answers (1)

ajk
ajk

Reputation: 4603

Henrique,

The reason you're getting that error is not related to GETDATE(), it has to do with indexes on columns from your CUSTOMER table. This bit from SQL Server 2008's SET Statements (Transact-SQL) document explains the issue in more detail:

When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.

Upvotes: 3

Related Questions