Solved

Alter SPs to have all SPs as  SET QUOTED_IDENTIFIER ON

Posted on 2014-09-06
7
329 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

839 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