SET QUOTED changing in Enterprise Manager

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SET QUOTED changing in Enterprise Manager

Author

Topic

jhilb
Starting Member

22 Posts

Posted - 2003-06-05 : 10:59:55

When editing a stored proc via Enterprise manager, my SET QUOTED INDENTIFIER seems to change.

If I script out the stored proc, it'll have 'SET QUOTED IDENTIFIER ON' in it, which is what I want. But i I edit that same proc via EntMan, it changes it to OFF.

This must be a setting somewhere?

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 11:10:26

It's not changing it, it's the default.

And why set it ON?

BOL:


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 Using Identifiers. 
Literals can be delimited by either single or double quotation marks.

Brett

8-)

Edited by - x002548 on 06/05/2003 11:10:56

jhilb
Starting Member

22 Posts

Posted - 2003-06-05 : 11:29:27

Long story... short version is that this server talks to another server running software that requires QUOTED IDENTIFIER to be on. It's been a major hassle.

Any way to change the 'default'?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-05 : 12:16:02

Why are you editing stored procedures in Enterprise Manager? You should use Query Analyzer instead. You can set the QUOTED_IDENTIFIER option in Query Analyzer very easy:

SET QUOTED_IDENTIFIER ON

Tara

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-06-05 : 15:33:29

What service pack are you on ? Enterprise Manager is not really the right tool for editing stored procedures and pre SP2 did not honour the Quoted Identifier setting.

FIX: Objects May Fail to Retain Quoted Identifier Setting When You Use SQL-DMO to Create or Alter Objects
[url]http://support.microsoft.com/default.aspx?scid=kb;en-us;306334[/url]

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources