Regardless of the literal character enclosure you use, you'll have problems
when you build SQL statements with character literals that include the
enclosure character. These need to be escaped with 2 consecutive enclosure
characters. For example:
SET QUOTED_IDENTIFIER OFF
SELECT "This is a "double-quoted" string"
--Incorrect syntax near the keyword 'double'.
GO
SELECT "This is a ""double-quoted"" string"
--works
GO
SET QUOTED_IDENTIFIER ON
SELECT 'This is a 'single-quoted' string'
--Line 2: Incorrect syntax near '-'.
GO
SELECT 'This is a ''single-quoted'' string'
--works
GO
QUOTED_IDENTIFIER ON is preferred because this is the ANSI standard. In the
case of SQL Server, this is one of the SET options that must be on in order
to use indexes on computed columns and views effectively so I suggest you
standardize on QUOTED_IDENTIFIER ON. You can make your application code
cleaner if you use parameters so that the API to takes care of this for you.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <th************@hotmail.com> wrote in message
news:72**************************@posting.google.c om...
Hello!
How bad is it to use SET QUOTED_IDENTIFIER OFF before an INSERT and
then switch it ON straight afterwards. So I can insert strings like
O'Neil etc.
It's my only design option at the moment. Everything works fine and as
far as I can see, it's only set to OFF for the current
connection/session so no other users will run into problems at the
same time if they need it ON, right?
Thanks Martin