Solved

Alter SPs to have all SPs as  SET QUOTED_IDENTIFIER ON

Posted on 2014-09-06
7
356 Views
Last Modified: 2014-09-08
In my database set of Stored Procedures has SET QUOTED_IDENTIFIER OFF. I would like to identify them and alter the SPs as SET QUOTED_IDENTIFIER ON. How to perform this with ease. Kindly suggest.
0
Comment
Question by:Easwaran Paramasivam
7 Comments
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
ID: 40307491
Please refer following article http://stackoverflow.com/questions/2147489/change-the-ansi-nulls-setting-for-all-stored-procedures-in-the-database
He has updated ansi null value , likely you can update quoted_identifier value
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 40307492
I saw that already. That is using SMO objects. Is there anyway using TSQL?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40307692
The easiest way is to script them all out are recreate.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 40308214
@Anthony - Could you please guide how to achieve your suggestion?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 350 total points
ID: 40308662
From SSMS :
1. Go to Object Explorer
2. Expand the Databases collection
3. Expand  your database node.
4. Expand Programmability
5. Select Stored Procedures
6. From the View menu select "Object Explorer Details" or simply press  F7
7. Select all the Stored Procedures you want here in the Object Explorer Details pane.
8. Right click one of the select Stored Procedures in the Object Explorer Details pane and select "Script Stored Procedures as"
9.  Select "Drop and Create To"
10.  Select "New Query Editor Window"
11.  In the new query window Replace all the instances of "SET QUOTED_IDENTIFIER OFF" with "SET QUOTED_IDENTIFIER ON"
12. Execute.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 40310242
You can identify only  the ones that have "QUOTED_IDENTIFIER OFF" using this query:

SELECT p.name
FROM sys.procedures p
WHERE
    OBJECTPROPERTYEX(p.object_id, 'ExecIsQuotedIdentOn') = 0

Then you can use "OBJECT_DEFINITION" in a simple cursor loop to ALTER them.  If you need help with that, just let me know.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 40311297
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 50
Separate 2 comma delimited columns into separate rows 2 46
tempdb log keep growing 7 44
Finding Where Clause Value in SQL Views and SP 21 35
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question