SET QUOTED_IDENTIFIER { ON | OFF }
Remarks
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double
quotation marks, and literals must be delimited by single quotation
marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted
and must follow all Transact-SQL rules for identifiers. For more
information, see Identifiers. Literals can be delimited by either single
or double quotation marks.
When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by
double quotation marks are interpreted as object identifiers. Therefore,
quoted identifiers do not have to follow the Transact-SQL rules for
identifiers. They can be reserved keywords and can include characters
not generally allowed in Transact-SQL identifiers. Double quotation
marks cannot be used to delimit literal string expressions; single
quotation marks must be used to enclose literal strings. If a single
quotation mark (') is part of the literal string, it can be represented
by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when
reserved keywords are used for object names in the database.
When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be
delimited by single or double quotation marks. If a literal string is
delimited by double quotation marks, the string can contain embedded
single quotation marks, such as apostrophes.
SET QUOTED_IDENTIFIER must be ON when you are creating or changing
indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER
is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with
indexes on computed columns or indexed views will fail. For more
information about required SET option settings with indexed views and
indexes on computed columns, see "Considerations When You Use the SET
Statements" in SET (Transact-SQL)
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f7e107f8-0fcf-408b-
b30f-da2323eeb714.htm> .
The SQL Native Client ODBC driver and SQL Native Client OLE DB Provider
for SQL Server automatically set QUOTED_IDENTIFIER to ON when
connecting. This can be configured in ODBC data sources, in ODBC
connection attributes, or OLE DB connection properties. The default for
SET QUOTED_IDENTIFIER is OFF for connections from DB-Library
applications.
When a table is created, the QUOTED IDENTIFIER option is always stored
as ON in the table's metadata even if the option is set to OFF when the
table is created.
When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET
ANSI_NULLS settings are captured and used for subsequent invocations of
that stored procedure.
When executed inside a stored procedure, the setting of SET
QUOTED_IDENTIFIER is not changed.
When SET ANSI_DEFAULTS is ON, SET QUOTED_IDENTIFIER is enabled.
SET QUOTED_IDENTIFIER also corresponds to the QUOTED_IDENTIFER setting
of ALTER DATABASE. For more information about database settings, see
ALTER DATABASE (Transact-SQL)
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/15f8affd-8f39-4021-
b092-0379fc6983da.htm> and Setting Database Options.
SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means
that if the SET statement is present in the batch or stored procedure,
it takes effect, regardless of whether code execution actually reaches
that point; and the SET statement takes effect before any statements are
executed.
Permissions
Requires membership in the public role.
Examples
A. Using the quoted identifier setting and reserved word object names
The following example shows that the SET QUOTED_IDENTIFIER setting must
be ON, and the keywords in table names must be in double quotation marks
to create and use objects that have reserved keyword names.
Copy Code
SET QUOTED_IDENTIFIER OFF
GO
-- An 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)
GO
SET QUOTED_IDENTIFIER ON
GO
-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT
NULL)
GO
SELECT "identity","order"
FROM "select"
ORDER BY "order"
GO
DROP TABLE "SELECT"
GO
SET QUOTED_IDENTIFIER OFF
GO
B. Using the quoted identifier setting with single and double quotation
marks
The following example shows the way single and double quotation marks
are used in string expressions with SET QUOTED_IDENTIFIER set to ON and
OFF.
Copy Code
SET QUOTED_IDENTIFIER OFF
GO
USE AdventureWorks
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Test')
DROP TABLE dbo.Test
GO
USE AdventureWorks
CREATE TABLE dbo.Test (ID INT, String VARCHAR(30))
GO
-- Literal strings can be in single or double quotation marks.
INSERT INTO dbo.Test VALUES (1, "'Text in single quotes'")
INSERT INTO dbo.Test VALUES (2, '''Text in 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")
GO
SET QUOTED_IDENTIFIER ON
GO
-- Strings inside double quotation marks are now treated
-- as object names, so they cannot be used for literals.
INSERT INTO dbo."Test" VALUES (7, 'Text with a single '' quote')
GO
-- Object identifiers do not have to be in double quotation marks
-- if they are not reserved keywords.
SELECT ID, String
FROM dbo.Test;
GO
DROP TABLE dbo.Test;
GO
SET QUOTED_IDENTIFIER OFF;
GO
Here is the result set.
Copy Code
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
http://www.windowsitpro.com/article/articleid/14025/why-cant-i-backupres
tore-my-sql-server-database-to-a-share-on-another-server.html
This will also be helpful, make sure your account that you are running
SQL Server and Agent service under has full admin rights to the share
you are trying to push the backups to. ( Share+ NTFS permissions)
Z
Edward E. Ziots
Netwok Engineer
Lifespan Organization
MCSE,MCSA,MCP,Security+,Network+,CCA
Phone: 401-639-3505
-----Original Message-----
From: Joseph L. Casale [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 28, 2008 12:45 PM
To: NT System Admin Issues
Subject: RE: Scripted SQL Backups
Salvador,
The maintenance plan is given the following errors:
This one I get all the time:
[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC
failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
These I get sporadically from different db's:
Backup can not be performed on database 'xxx'. This sub
task is ignored.
Any ideas how to troubleshoot this?
Thanks!
jlc
From: Salvador Manzo [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 26, 2008 4:30 PM
To: NT System Admin Issues
Subject: Re: Scripted SQL Backups
MS SQL 2000
Database Maintenance Plans
MS SQL 2005
Database Maintenance Wizard plus some custom SSIS tasks if you'll also
be doing differentials. (Microsoft made this interface needlessly
confusing, IMO).
SQLExpress (also applies to MSDE)
http://www.sqldbatips.com/showarticle.asp?ID=27
http://www.sqldbatips.com/showarticle.asp?ID=29
MySQL
http://dev.mysql.com/doc/refman/5.0/en/backup.html
http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Dat
abase/
On 2/26/08 3:17 PM, "Joseph L. Casale" <[EMAIL PROTECTED]>
wrote:
I'll admit I have _never_ used sql am not sure what to do here.
I have a slew of small DB's I need to script a nightly full of to a
directory which gets backed up to tape.
Can someone push me in the right direction, being that I don't really
know anything about SQL, I don't know if the various points I am reading
are correct/optimal :-)
Thanks!
jlc
-----
Salvador Manzo [ 620 W. 35th St - Los Angeles, CA 90089 e.
[EMAIL PROTECTED] ]
Auxiliary Services IT, Datacenter
University of Southern California
818-612-5112
The injury which may possibly be done by defeating a few good laws, will
be amply compensated by the advantage of preventing a number of bad
ones. -- Alexander Hamilton, Federalist No. 73 on the veto power
~ Upgrade to Next Generation Antispam/Antivirus with Ninja! ~
~ <http://www.sunbelt-software.com/SunbeltMessagingNinja.cfm> ~<<image001.gif>>
