Solved

Alter SPs to have all SPs as  SET QUOTED_IDENTIFIER ON

Posted on 2014-09-06
7
318 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL server is using more virtual memory. 5 84
Sql query 107 63
How to search for strings inside db views 4 27
Need help with a Stored Proc on Sql Server 2012 4 10
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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

773 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