'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.
The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>

'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the
proc doesn't change
anything. You have to set this in the connection where you *create* the proc
edure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Lin
e
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>

'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>

QUOTED_IDENTIFIER and SQL DMO

I am trying to dump out scripts for stored procedure and wondering how
I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output.
I gather this option becomes part of stored procedure script on
creation time...!
I couldn't find any flags in SQL DMO that could be used to ignore this
option.
Please help...
Thanks
Sandiyanhi Sandiyan,
"sandiyan" <sandiyan@.yahoo.co.uk> ha scritto nel messaggio
news:69e9c64b.0404150614.632ccd9d@.posting.google.com...
> I am trying to dump out scripts for stored procedure and wondering how
> I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output.
> I gather this option becomes part of stored procedure script on
> creation time...!
> I couldn't find any flags in SQL DMO that could be used to ignore this
> option.
> Please help...
i think the Object scripting constant
SQLDMO_SCRIPT_TYPE.SQLDMOScript_UseQuotedIdentifiers shoul'd be your case...
SQLDMOScript_UseQuotedIdentifiers , vaule= -1, description=Use quote
characters to delimit identifier parts when scripting object names
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||> i think the Object scripting constant
> SQLDMO_SCRIPT_TYPE.SQLDMOScript_UseQuotedIdentifiers shoul'd be your case...
> SQLDMOScript_UseQuotedIdentifiers , vaule= -1, description=Use quote
> characters to delimit identifier parts when scripting object names
Thanks. I tried this and it didn't work.
Help...
regards,
Sandiyan.

QUOTED_IDENTIFIER and SQL DMO

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:339290 microsof
t.public.sqlserver.programming:440604
I am trying to dump out scripts for stored procedure and wondering how
I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output.
I gather this option becomes part of stored procedure script on
creation time...!
I couldn't find any flags in SQL DMO that could be used to ignore this
option.
Please help...
Thanks
Sandiyanhi Sandiyan,
"sandiyan" <sandiyan@.yahoo.co.uk> ha scritto nel messaggio
news:69e9c64b.0404150614.632ccd9d@.posting.google.com...
> I am trying to dump out scripts for stored procedure and wondering how
> I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output.
> I gather this option becomes part of stored procedure script on
> creation time...!
> I couldn't find any flags in SQL DMO that could be used to ignore this
> option.
> Please help...
i think the Object scripting constant
SQLDMO_SCRIPT_TYPE.SQLDMOScript_UseQuotedIdentifiers shoul'd be your case...
SQLDMOScript_UseQuotedIdentifiers , vaule= -1, description=Use quote
characters to delimit identifier parts when scripting object names
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||> i think the Object scripting constant
> SQLDMO_SCRIPT_TYPE.SQLDMOScript_UseQuotedIdentifiers shoul'd be your case.
.
> SQLDMOScript_UseQuotedIdentifiers , vaule= -1, description=Use quote
> characters to delimit identifier parts when scripting object names
Thanks. I tried this and it didn't work.
Help...
regards,
Sandiyan.sql

QUOTED_IDENTIFIER and SQL DMO

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:339290 microsoft.public.sqlserver.programming:440604
I am trying to dump out scripts for stored procedure and wondering how
I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output.
I gather this option becomes part of stored procedure script on
creation time...!
I couldn't find any flags in SQL DMO that could be used to ignore this
option.
Please help...
Thanks
Sandiyan
hi Sandiyan,
"sandiyan" <sandiyan@.yahoo.co.uk> ha scritto nel messaggio
news:69e9c64b.0404150614.632ccd9d@.posting.google.c om...
> I am trying to dump out scripts for stored procedure and wondering how
> I could exclude 'SET QUOTED_IDENTIFIER ON/OFF' option from the output.
> I gather this option becomes part of stored procedure script on
> creation time...!
> I couldn't find any flags in SQL DMO that could be used to ignore this
> option.
> Please help...
i think the Object scripting constant
SQLDMO_SCRIPT_TYPE.SQLDMOScript_UseQuotedIdentifie rs shoul'd be your case...
SQLDMOScript_UseQuotedIdentifiers , vaule= -1, description=Use quote
characters to delimit identifier parts when scripting object names
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> i think the Object scripting constant
> SQLDMO_SCRIPT_TYPE.SQLDMOScript_UseQuotedIdentifie rs shoul'd be your case...
> SQLDMOScript_UseQuotedIdentifiers , vaule= -1, description=Use quote
> characters to delimit identifier parts when scripting object names
Thanks. I tried this and it didn't work.
Help...
regards,
Sandiyan.

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank you
Take a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank youTake a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER and ARITHABORT for Maintenance plans

Hi,
I would like to setup a maintenance job to 'Rebuild Indexes' as my
'Optimization Job' keeps failing on "DBCC failed because the following set
options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
What command do I need to use to rebuild all the indexes on all the tables.
I see DBCC DBREINDEX but that needs table name as a parameter. Any kind of
help is greatly appreciated.
Thank youTake a look at DBCC SHOWCONTIG in BooksOnLine for the sample near the
bottom. This will rebuild the indexes that actually require it given
certain percentage of fragmentation.
--
Andrew J. Kelly SQL MVP
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:7DB5F397-1671-4F97-9585-AC7C20382FA1@.microsoft.com...
> Hi,
> I would like to setup a maintenance job to 'Rebuild Indexes' as my
> 'Optimization Job' keeps failing on "DBCC failed because the following set
> options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> What command do I need to use to rebuild all the indexes on all the
> tables.
> I see DBCC DBREINDEX but that needs table name as a parameter. Any kind
> of
> help is greatly appreciated.
> Thank you

QUOTED_IDENTIFIER & ANSI_NULLS

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.
Ted
I'm affraid you cannot. What is your concern?
"Ted Theo" <tedtheo@.gmail.com> wrote in message
news:4b68c64c-e5ab-4024-878b-dbeec155abee@.d21g2000prf.googlegroups.com...
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
|||Ted Theo (tedtheo@.gmail.com) writes:
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
There does not seem to be an option for this.
The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)
Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Dec 2, 5:58 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Ted Theo (tedt...@.gmail.com) writes:
> There does not seem to be an option for this.
>
it's just a bit of a nuisance like you said. i have more projects
that don't use source control (single dev projects) than ones that do
so i encounter it frequently. i have a high level understanding of
what both options accomplish and i haven't found a case where setting
them at the individual object level has been advantageous. maybe i'm
just missing that part.
it seems sql server management studio just turns these settings on
when scripting an object and doesn't turn them off. is there a way to
turn this behavior off in mgmt studio? is there a reason i wouldn't
want to do this?

> The reason they are there, is that these to set options are saved with
> the procedure. I can understand that it is a bit of a nuisance. But since
> Enterprise Manager incorrectly has these two off by default, it's
> probably a good thing that QA includes them with the right setting. (But
> it's not good that there is a SET OFF for one of them at the end.)
> Personally, I don't find this a hassle, since I keep my code under source
> control, and rarely have reason to script it from the database.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||>> is there a reason I wouldn't want to do this? <<
Conformance to ANSI/ISO Standards should be a goal in any shop, so you
would not turn off options that bring you to that goal. Why would you
want to write your own database language?
|||Ted Theo (tedtheo@.gmail.com) writes:
> it's just a bit of a nuisance like you said. i have more projects
> that don't use source control (single dev projects) than ones that do
> so i encounter it frequently. i have a high level understanding of
> what both options accomplish and i haven't found a case where setting
> them at the individual object level has been advantageous. maybe i'm
> just missing that part.
Like it or not, the settings of the two *are* saved with each procedure.
This is in difference from, say, ANSI_WARNINGS, where the run-time setting
of the two apply.
As for which of the two settings to use, keep in mind that there are
features in SQL Server that are not available if any of ANSI_NULLS
or QUOTED_IDENTIFIER are off:
o Indexed views and index on computed columns.
o Xquery.
o Queries involving linked servers (ANSI_NULLS only).
Of course, if you use default settings etc, there should never be any
reason to include these in the script, because it should be a rare
exception that you deliberately would create a procedure with any of
them off. (The only half-good reason I can think of is that you work
with dynamic SQL in several layers and nesting quotes is driving you
crazy. Turning off QUOTED_IDENTIFIERS permits you to use " as a string
delimiter as well to save your sanity.)

> it seems sql server management studio just turns these settings on
> when scripting an object and doesn't turn them off. is there a way to
> turn this behavior off in mgmt studio? is there a reason i wouldn't
> want to do this?
The fact that SSMS do not set them OFF, is probably my fault. I bitched
about that during the beta of SQL 2005.
No, neither SSMS appears to have an option for this, just like QA there
is only an option for controlling whether ANSI_PADDING should be
scripted tables.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:29789bc2-91b0-4f7a-a593-547d76942965@.b15g2000hsa.googlegroups.com...
> Conformance to ANSI/ISO Standards should be a goal in any shop, so you
> would not turn off options that bring you to that goal. Why would you
> want to write your own database language?
The goal here is helping people with their queries, and NOT telling them
they should be a "BY THE BOOK" kind of STIFF like yourself.
Just FYI
sql

QUOTED_IDENTIFIER & ANSI_NULLS

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.
Ted
I'm affraid you cannot. What is your concern?
"Ted Theo" <tedtheo@.gmail.com> wrote in message
news:4b68c64c-e5ab-4024-878b-dbeec155abee@.d21g2000prf.googlegroups.com...
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
|||Ted Theo (tedtheo@.gmail.com) writes:
> does anyone know how to keep QA from adding the lines setting these
> two options on and off along with blank lines at the beginning and end
> of every object you edit? i have searched quite a bit on this but
> haven't been able to come up with anything.
There does not seem to be an option for this.
The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)
Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||On Dec 2, 5:58 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> Ted Theo (tedt...@.gmail.com) writes:
> There does not seem to be an option for this.
>
it's just a bit of a nuisance like you said. i have more projects
that don't use source control (single dev projects) than ones that do
so i encounter it frequently. i have a high level understanding of
what both options accomplish and i haven't found a case where setting
them at the individual object level has been advantageous. maybe i'm
just missing that part.
it seems sql server management studio just turns these settings on
when scripting an object and doesn't turn them off. is there a way to
turn this behavior off in mgmt studio? is there a reason i wouldn't
want to do this?

> The reason they are there, is that these to set options are saved with
> the procedure. I can understand that it is a bit of a nuisance. But since
> Enterprise Manager incorrectly has these two off by default, it's
> probably a good thing that QA includes them with the right setting. (But
> it's not good that there is a SET OFF for one of them at the end.)
> Personally, I don't find this a hassle, since I keep my code under source
> control, and rarely have reason to script it from the database.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||>> is there a reason I wouldn't want to do this? <<
Conformance to ANSI/ISO Standards should be a goal in any shop, so you
would not turn off options that bring you to that goal. Why would you
want to write your own database language?
|||Ted Theo (tedtheo@.gmail.com) writes:
> it's just a bit of a nuisance like you said. i have more projects
> that don't use source control (single dev projects) than ones that do
> so i encounter it frequently. i have a high level understanding of
> what both options accomplish and i haven't found a case where setting
> them at the individual object level has been advantageous. maybe i'm
> just missing that part.
Like it or not, the settings of the two *are* saved with each procedure.
This is in difference from, say, ANSI_WARNINGS, where the run-time setting
of the two apply.
As for which of the two settings to use, keep in mind that there are
features in SQL Server that are not available if any of ANSI_NULLS
or QUOTED_IDENTIFIER are off:
o Indexed views and index on computed columns.
o Xquery.
o Queries involving linked servers (ANSI_NULLS only).
Of course, if you use default settings etc, there should never be any
reason to include these in the script, because it should be a rare
exception that you deliberately would create a procedure with any of
them off. (The only half-good reason I can think of is that you work
with dynamic SQL in several layers and nesting quotes is driving you
crazy. Turning off QUOTED_IDENTIFIERS permits you to use " as a string
delimiter as well to save your sanity.)

> it seems sql server management studio just turns these settings on
> when scripting an object and doesn't turn them off. is there a way to
> turn this behavior off in mgmt studio? is there a reason i wouldn't
> want to do this?
The fact that SSMS do not set them OFF, is probably my fault. I bitched
about that during the beta of SQL 2005.
No, neither SSMS appears to have an option for this, just like QA there
is only an option for controlling whether ANSI_PADDING should be
scripted tables.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:29789bc2-91b0-4f7a-a593-547d76942965@.b15g2000hsa.googlegroups.com...
> Conformance to ANSI/ISO Standards should be a goal in any shop, so you
> would not turn off options that bring you to that goal. Why would you
> want to write your own database language?
The goal here is helping people with their queries, and NOT telling them
they should be a "BY THE BOOK" kind of STIFF like yourself.
Just FYI

QUOTED_IDENTIFIER & ANSI_NULLS

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.Ted (tedtheo@.gmail.com) writes:

Quote:

Originally Posted by

does anyone know how to keep QA from adding the lines setting these
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.

I repeat the answer I posted in another newsgroup in reponse to the same
question:

There does not seem to be an option for this.

The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)

Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

quoted_identifier

How do I see the current value of QUOTED_IDENTIFIER in a SQL2000
database? Thank you.Select OBJECTPROPERTY(DB_ID(),'IsQuotedIdentOn'
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> schrieb im
Newsbeitrag news:MPG.1cd87166eb1a31ef9898d6@.msnews.microsoft.com...
> How do I see the current value of QUOTED_IDENTIFIER in a SQL2000
> database? Thank you.|||You can determine the database default QUOTED_IDENTIFIER setting is on with
DATABASEPROPERTY. However, connection settings take precedence and
QUOTED_IDENTIFIER ON is the default OLEDB and ODBC connection setting.
SELECT DATABASEPROPERTY('MyDatabase', 'IsQuotedIdentifiersEnabled')
Hope this helps.
Dan Guzman
SQL Server MVP
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cd87166eb1a31ef9898d6@.msnews.microsoft.com...
> How do I see the current value of QUOTED_IDENTIFIER in a SQL2000
> database? Thank you.|||In article <uIVaoSsSFHA.2128@.TK2MSFTNGP14.phx.gbl>, guzmanda@.nospam-
online.sbcglobal.net says...
> You can determine the database default QUOTED_IDENTIFIER setting is on wit
h
> DATABASEPROPERTY. However, connection settings take precedence and
> QUOTED_IDENTIFIER ON is the default OLEDB and ODBC connection setting.
> SELECT DATABASEPROPERTY('MyDatabase', 'IsQuotedIdentifiersEnabled')
>
Thanks. The above SELECT statement returns 0 on my database which I
assume indicates that quoted_identifier is OFF. But can you explain
what you mean when you say 'connection settings take precedence'? Are
'connection settings' something different from the DATABASE setting
returned by this SELECT statement? I am accessing SQLServer through my
application using OLEDB. Thanks much for your help.|||Check out the SET command. For some database options, you have the same beha
vior exposed through a
SET command. A setting though a SET command applies to a connection ("sessio
n"). Some API's will set
some SET commands for you. And I believe that both ODBC and OLEDB will set t
he quoted_identifier
setting, making the database option useless for those API's.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cd9641ac7ff34569898d7@.msnews.microsoft.com...
> In article <uIVaoSsSFHA.2128@.TK2MSFTNGP14.phx.gbl>, guzmanda@.nospam-
> online.sbcglobal.net says...
> Thanks. The above SELECT statement returns 0 on my database which I
> assume indicates that quoted_identifier is OFF. But can you explain
> what you mean when you say 'connection settings take precedence'? Are
> 'connection settings' something different from the DATABASE setting
> returned by this SELECT statement? I am accessing SQLServer through my
> application using OLEDB. Thanks much for your help.|||Ah... So you're saying both a SET command *and* the API will set the
connection properties *for the session*, as opposed to the database
setting. And that even if the database setting for quoted_identifier is
OFF, the OLEDB interface sets it ON for any session in which it's used.
Is that right?
In article <OWknqczSFHA.2872@.TK2MSFTNGP14.phx.gbl>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> Check out the SET command. For some database options, you have the same be
havior exposed through a
> SET command. A setting though a SET command applies to a connection ("sess
ion"). Some API's will set
> some SET commands for you. And I believe that both ODBC and OLEDB will set
the quoted_identifier
> setting, making the database option useless for those API's.
>|||Yes, The API's actually executes a SET command for you (or at least to the s
ame effect).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cd984b62c37b7169898d8@.msnews.microsoft.com...
> Ah... So you're saying both a SET command *and* the API will set the
> connection properties *for the session*, as opposed to the database
> setting. And that even if the database setting for quoted_identifier is
> OFF, the OLEDB interface sets it ON for any session in which it's used.
> Is that right?
> In article <OWknqczSFHA.2872@.TK2MSFTNGP14.phx.gbl>,
> tibor_please.no.email_karaszi@.hotmail.nomail.com says...

QUOTED_IDENTIFIER

Hi.
In Query Analyser I ran:
SET QUOTED_IDENTIFIER OFF
GO
EXECUTE sp1
GO
where SP1 included:
SET QUOTED_IDENTIFIER OFF
...
IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
EXECUTE ('ALTER VIEW view1...') END
IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
EXECUTE ('ALTER VIEW view2...') END
Both IF conditions were TRUE.
Both views ended up with QUOTED_IDENTIFIER ON.
How can I ensure that both views end up with QUOTED_IDENTIFIER OFF?
Thanks in advance.
--
Peter HyssettThis is a "sticky option", so you need to set it correctly when you *create*
the procedure. Setting
it at runtime accomplishes nothing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:8E515BA3-E727-4AD6-8B30-0386B5BB0220@.microsoft.com...
> Hi.
> In Query Analyser I ran:
> SET QUOTED_IDENTIFIER OFF
> GO
> EXECUTE sp1
> GO
> where SP1 included:
> SET QUOTED_IDENTIFIER OFF
> ...
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view1...') END
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view2...') END
> Both IF conditions were TRUE.
> Both views ended up with QUOTED_IDENTIFIER ON.
> How can I ensure that both views end up with QUOTED_IDENTIFIER OFF?
> Thanks in advance.
> --
> Peter Hyssett|||By "sticky option", that means that the the settings you had for
quoted_ident, and ansi_nulls when you created the stored procedure, stick
and are saved within the sp. The SP then runs with those option settings
regardless of how the option is set at run-time..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter Hyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:8E515BA3-E727-4AD6-8B30-0386B5BB0220@.microsoft.com...
> Hi.
> In Query Analyser I ran:
> SET QUOTED_IDENTIFIER OFF
> GO
> EXECUTE sp1
> GO
> where SP1 included:
> SET QUOTED_IDENTIFIER OFF
> ...
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view1...') END
> IF (...) BEGIN SET QUOTED_IDENTIFIER OFF
> EXECUTE ('ALTER VIEW view2...') END
> Both IF conditions were TRUE.
> Both views ended up with QUOTED_IDENTIFIER ON.
> How can I ensure that both views end up with QUOTED_IDENTIFIER OFF?
> Thanks in advance.
> --
> Peter Hyssett

quoted_identifier

Hi
I am getting this strange error :
Server: Msg 1934, Level 16, State 1, Procedure
dataIntegration_mergeCompanyData_prc, Line 90
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'
the problem seem to be with a table in an update statement in the proc where
it references a table in an index view. if I change the left outer join of
the update statement to an inner join, the error goes away, if I uncomment
the schema_binding in the index view, the error also goes away.
anyone know what is going on?
thanks
PAny time you access an indexed view the connection must have certain SET
settings such as QUOTED_IDENTIFIER set a certain way. Look up Indexed Views
and then Set options that affect results underneath that for details.
Andrew J. Kelly SQL MVP
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:79578F94-688A-4A17-B8BA-D6418C437AEA@.microsoft.com...
> Hi
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where
> it references a table in an index view. if I change the left outer join
> of
> the update statement to an inner join, the error goes away, if I uncomment
> the schema_binding in the index view, the error also goes away.
> anyone know what is going on?
> thanks
> P
>|||alfred (alfred@.discussions.microsoft.com) writes:
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where it references a table in an index view. if I change the left
> outer join of the update statement to an inner join, the error goes
> away, if I uncomment the schema_binding in the index view, the error
> also goes away.
> anyone know what is going on?
As Andrew said, you are performing an update that affects an indexed
view. Whenever the you work with an indexed view, these settings must
be on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDNING,
CONCAT_NULL_YIELDS_NULL and ARITHABORT. All but the last option are
on by default when you connect with any API but DB-Library.
However, for the first two settings, what applies when you run a stored
procedure is not the setting for the connection, as these settings are
saved with the stored procedure.
And very unfortunate, there are two tools for which QUOTED_IDENTIFIER
is off by default: OSQL and Enterprise Manager (the latter also has
ANSI_NULLS off by default). Therefore, if you use these tools, you
must take precautions to make sure that this setting is on. If you
use OSQL, use the -I option to turn on QUOTED_IDENFIER. If you use
Enterprise Manager to edit your procedures, simply stop doing that
and use Query Analyzer instead. QA does not have this issue, and is
better editor anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
Message posted via http://www.droptable.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David

Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the followin
g
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
Message posted via http://www.droptable.com"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David|||Also, the database option is largely useless as all modern APIs (and tools)
will override the
database settings.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:%23TX5k4kGHHA.4920@.TK2MSFTNGP05.phx.gbl...
>
> "cbrichards via droptable.com" <u3288@.uwe> wrote in message news:6a6bb12d
8cd92@.uwe...
>
> For the database you will have to monitor and complain, since creating dat
abases cannot be rolled
> back.
> For stored procedures you can add a DDL Trigger in each database that will
prevent stored
> procedure creation or modification from a connection that does not have th
e required setting. EG
> CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
> ON database
> FOR CREATE_PROCEDURE, ALTER_PROCEDURE
> AS
> begin
> declare @.quoted_identifiers varchar(5)
> set @.quoted_identifiers = EVENTDATA().value(
> '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
> 'nvarchar(max)')
> if @.quoted_identifiers <> 'ON'
> begin
> raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a p
rocedure.',16,1)
> end
> --select @.quoted_identifiers quoted_identifiers
> end
> GO
> Then
> SET QUOTED_IDENTIFIER off
> go
> create procedure foo
> as
> select 1 a
> Fails with
> Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifier
s, Line 13
> You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
>
> David
>

Quoted Identifier ON as default

Currently my user databases, and model database, all have QUOTED IDENTIFER
set to false.
I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
conditions:
1. When a stored procedure is created (assuming the connection that is
performing the create, allows database default settings) to use the database
default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
statement when the procedure is created or altered, but instead it uses the
database default setting.
2. When a new database is created, its default setting is QUOTED IDENTIFIERS
ENABLED = TRUE.
How would one go about doing this?
--
Message posted via http://www.sqlmonster.com"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6a6bb12d8cd92@.uwe...
> Currently my user databases, and model database, all have QUOTED IDENTIFER
> set to false.
> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the
> following
> conditions:
> 1. When a stored procedure is created (assuming the connection that is
> performing the create, allows database default settings) to use the
> database
> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
> statement when the procedure is created or altered, but instead it uses
> the
> database default setting.
> 2. When a new database is created, its default setting is QUOTED
> IDENTIFIERS
> ENABLED = TRUE.
> How would one go about doing this?
For the database you will have to monitor and complain, since creating
databases cannot be rolled back.
For stored procedures you can add a DDL Trigger in each database that will
prevent stored procedure creation or modification from a connection that
does not have the required setting. EG
CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
ON database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE
AS
begin
declare @.quoted_identifiers varchar(5)
set @.quoted_identifiers = EVENTDATA().value(
'(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
'nvarchar(max)')
if @.quoted_identifiers <> 'ON'
begin
raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a
procedure.',16,1)
end
--select @.quoted_identifiers quoted_identifiers
end
GO
Then
SET QUOTED_IDENTIFIER off
go
create procedure foo
as
select 1 a
Fails with
Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers,
Line 13
You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
David|||Also, the database option is largely useless as all modern APIs (and tools) will override the
database settings.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:%23TX5k4kGHHA.4920@.TK2MSFTNGP05.phx.gbl...
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6a6bb12d8cd92@.uwe...
>> Currently my user databases, and model database, all have QUOTED IDENTIFER
>> set to false.
>> I want to help ensure that QUOTED_IDENTIFIER is set to ON under the following
>> conditions:
>> 1. When a stored procedure is created (assuming the connection that is
>> performing the create, allows database default settings) to use the database
>> default settings. In other words, there is not a SET QUOTED_IDENTIFIER ON
>> statement when the procedure is created or altered, but instead it uses the
>> database default setting.
>> 2. When a new database is created, its default setting is QUOTED IDENTIFIERS
>> ENABLED = TRUE.
>> How would one go about doing this?
>
> For the database you will have to monitor and complain, since creating databases cannot be rolled
> back.
> For stored procedures you can add a DDL Trigger in each database that will prevent stored
> procedure creation or modification from a connection that does not have the required setting. EG
> CREATE TRIGGER ddl_trig_enforce_quoted_identifiers
> ON database
> FOR CREATE_PROCEDURE, ALTER_PROCEDURE
> AS
> begin
> declare @.quoted_identifiers varchar(5)
> set @.quoted_identifiers = EVENTDATA().value(
> '(/EVENT_INSTANCE/TSQLCommand/SetOptions/@.QUOTED_IDENTIFIER)[1]',
> 'nvarchar(max)')
> if @.quoted_identifiers <> 'ON'
> begin
> raiserror('You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.',16,1)
> end
> --select @.quoted_identifiers quoted_identifiers
> end
> GO
> Then
> SET QUOTED_IDENTIFIER off
> go
> create procedure foo
> as
> select 1 a
> Fails with
> Msg 50000, Level 16, State 1, Procedure ddl_trig_enforce_quoted_identifiers, Line 13
> You must use SET QUOTED_IDENTIFIER ON to create or alter a procedure.
>
> David
>sql