Solved

Alter SPs to have all SPs as  SET QUOTED_IDENTIFIER ON

Posted on 2014-09-06
7
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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.
In this article I will describe the Copy Database Wizard 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.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

734 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