Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently working with a MSSQL 2008R2 Database which uses the following SET-Commands frequently:

SET ANSI_NULLS { ON | OFF }
SET QUOTED_IDENTIFIER { ON | OFF }
SET ANSI_PADDING { ON | OFF }

While I was researching these Settings I stumbled across the following Note for ANSI_NULLS on Microsofts technet (Link):

In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Because of this I would like to enforce a "global" Setting. I have been searching for the better part of a day but as I understand it you can always change these settings. To be completely sure I would like to ask here:

Is it possible to either enforce a Setting like ANSI_NULLS ON or to disable the possibility of changing these Settings on "any" level. I would like to use this to test if the Database would "survive" if the ability to set a specific option to OFF is disabled.

I hope I was able to describe my problem. This is not my first language. Thanks in advance.

PS: On StackOverflow I was advised to Ask this Question here too. Original Question: Here

share|improve this question

1 Answer 1

up vote 6 down vote accepted

There is no way to enforce a global setting or prevent people running

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF

You can get a quick idea if your application is using any deprecated features by running

SELECT *
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated%'
       AND cntr_value > 0
ORDER  BY cntr_value DESC 

If you do find areas of concern then you could set up a Server Side trace capturing the deprecation events.

enter image description here

This also returns the session id and SQL Handle of the offending batch to allow you to investigate further if you do see any events.

A slightly more sophisticated approach would be to set up a notification procedure to retrieve the text associated with the SQL Handle more quickly to reduce the probability that the offending SQL is no longer in the cache when you come to analyse the results.

Or you could probably use extended events to retrieve the TSQL along with the event.

share|improve this answer
    
First off let me say thank you for that answer! Second: Does this also apply to Quoted Identifiers? –  DLA2014 Jan 14 at 13:29
    
@DLA2014 - That isn't deprecated so no it doesn't show up in any of the deprecation events. –  Martin Smith Jan 14 at 13:30
    
Could you tell me how you set up such a Server Side trace? I am somewhat new at this. –  DLA2014 Jan 14 at 13:56
    
@DLA2014 - If you're going to learn something new much better to learn extended events. Take a look at the article here. Seems to do what is needed from a quick look. –  Martin Smith Jan 14 at 14:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.