So the single quotes in the database were added in.

Causes SQL Server to follow the ISO rules for quotation-delimited identifiers and literal strings. Identifiers delimited by double quotation marks can be Transact-SQL reserved keywords, or they can contain characters that are normally disallowed by the Transact-SQL identifier syntax conventions.

Transact-SQL Syntax conventions

Grammar

SET QUOTED_IDENTIFIER {on | OFF}

Notes

When SET QUOTED_IDENTIFIER is on, identifiers can be delimited by double quotation marks, and literals must be separated by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers are not quoted and must conform to all Transact-SQL identifier rules. For more information, see identifiers. Text can be delimited by single or double quotation marks.

When SET QUOTED_IDENTIFIER is on (the default value), all strings separated by double quotation marks are interpreted as object identifiers. Therefore, the quoted identifiers do not have to conform to the Transact-SQL identifier rules. They can be reserved keywords and can contain characters that are not normally allowed in Transact-SQL identifiers. You cannot use double quotation marks to delimit literal string expressions, but you must enclose literal strings in single quotation marks. If the single quotation mark (') is part of a literal string, it can be represented by two single quotation marks ("). The SET quoted_identifier must be on when reserved keywords are used for object names in the database.

When SET QUOTED_IDENTIFIER is OFF, the literal strings in the expression can be separated by either single or double quotation marks. If the literal string is delimited by double quotation marks, you can include embedded single quotation marks, such as ellipses, in the string.

When you create or change an index on a computed column or indexed view, the SET quoted_identifier must be on. If SET QUOTED_IDENTIFIER is OFF, the CREATE, UPDATE, INSERT, and DELETE statements on the indexed table on the computed column or indexed view will fail. For more information about the SET option settings that are required to calculate the indexed views and indexes for a column, see "Considerations when using Set statements" in SET (Transact-SQL).

When you make a connection, the SQL Server Native client ODBC driver and SQL Server Native client OLE DB Provider for SQL Server automatically set the QUOTED_IDENTIFIER Reset to ON. This can be configured in an ODBC data source, an ODBC connection attribute, or an OLE DB connection attribute. For connections from Db-library applications, SET QUOTED_IDENTIFIER is set to OFF by default.

When you create a table, this option is always stored as on in the metadata for the table, even if the QUOTED IDENTIFIER option is set to OFF at this time.

When you create a stored procedure, the SET QUOTED_IDENTIFIER and set ANSI_NULLS settings are captured and used for subsequent calls to the stored procedure.

When you execute a SET QUOTED_IDENTIFIER within a stored procedure, its settings do not change.

When set ANSI_DEFAULTS is on, SET QUOTED_IDENTIFIER is enabled.

The SET QUOTED_IDENTIFIER also corresponds to the Quoted_identifer setting of the ALTER DATABASE. For more information about database settings, see ALTER database (Transact-SQL) and set databases options.

SET QUOTED_IDENTIFIER is configured at the time of analysis. Setting at parse time means that the SET statement takes effect whenever it appears in a batch or stored procedure, regardless of whether code execution actually reaches that point, and the SET statement takes effect before any statements are executed.

Permissions

Requires membership with the public role.

ExampleA. Using quoted identifiers to set and preserve Word object names

The following example shows SET QUOTED_IDENTIFIER that the settings must be ON , and that the keywords within the table name must be within double quotation marks to create and use objects with reserved keyword names.

Copy Code

SET QUOTED_IDENTIFIER offgo--A attempt to create a table with a reserved keyword as a name--should fail. CREATE TABLE "Select" ("Identity" int identity not NULL, "order" int. NOT NULL); Goset QUOTED_IDENTIFIER on; go--would succeed. CREATE TABLE "Select" ("Identity" int identity not NULL, "order" int. NOT NULL); Goselect "Identity", "Order" from "Select" Order By "order"; Godrop TABLE "select"; Goset QUOTED_IDENTIFIER OFF; GO

B. Using an identifier set with single and double quotation marks

The following example shows SET QUOTED_IDENTIFIER ON OFF how single and double quotation marks are used in a string expression when set to and.

Copy Code

SET QUOTED_IDENTIFIER OFF; Gouse adventureworks2008r2;if EXISTS (SELECT table_name from INFORMATION_SCHEMA. TABLES WHERE table_name = ' Test ') DROP TABLE dbo. Test; Gouse adventureworks2008r2; CREATE TABLE dbo. Test (ID INT, String VARCHAR (30)); go--Literal strings Can is in a single or double quotation marks. INSERT into dbo. Test VALUES (1, "' Text in a single quotes '); INSERT into dbo. Test VALUES (2, ' ' Text in a single quotes '); INSERT into dbo. Test VALUES (3, ' Text with 2 ' "single quotes '); INSERT into dbo. Test VALUES (4, ' "Text in double quotes"); INSERT into dbo. Test VALUES (5, "" "Text in double quotes" "); INSERT into dbo. Test VALUES (6, "Text with 2" "" "double quotes"); Goset QUOTED_IDENTIFIER on; go--Strings inside double quotation marks is now treated – as object names, so they cannot is used for literals. INSERT into dbo. " Test "VALUES (7, ' Text with a single ' quote '); go--Object identifiers does not have the to is in double quotation marks--if they is not reserved keywords. SELECT ID, String from dbo. Test; Godrop TABLE dbo. Test; Goset QUOTED_IDENTIFIER OFF; GO

Here is the result set:

ID String

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

1 ' Text in single quotes '

2 ' Text in single quotes '

3 Text with 2 "single quotes

4 "Text in double quotes"

5 "Text in double quotes"

6 Text with 2 "" double quotes

7 Text with a single ' quote