Get Your ANSI_NULLs Settings Consistent

  • And don't forget while you're fixing all the =NULL and <>NULL, you need to also look for this:

    CASE <fieldname or expression> WHEN NULL THEN ..... since this is treated as =NULL


  • This article was the key to helping me resolve an issue my team was facing when we upgraded our Linux proxy servers to 64-bit SLES 11, from 32-bit SLES10 SP2. Many of our tools (written in Perl, connecting to SQL Server 2005 server via Linux proxies) were not returning results from our databases, and were not able to execute some stored procedures or make data modifications. No immediate errors were presenting and running the queries directly on our SQL Server 2005 server executed without an issue.

    After a lot of debugging and googling, I stumbled across this article and tested it out. This seems to have solved our problems in our test environment. I am planning on deploying my changes to production when are testing has completed though, and I was hoping someone here could tell me whether or not I need to stop our services prior to rolling out. I did not stop services on our test database server, but I also didn't have any tools running while I was making the necessary changes. Is there a problem with making the changes necessary live? We have a 24/7 uptime environment, so it would be a hard sell here for me to require that we go down while I make the changes. However, if I am taking a big risk by making our databases and schema ANSI compliant live, then I will make the case. I just need a little education on the topic. Any resources and/or advice would be greatly appreciated.

    Thank you in advance and thank you for this article!


  • Very helpful article. I now have my databases all set for ANSI compliance. The only issue that I have is that the system stored procedures are not ANSI compliant. Is there any configuration setting to make them ANSI, or do I need to modify the ones I use manually?

    eg. [sys].[sp_MSforeachtable] has SET QUOTED_IDENTIFIER OFF

    Is there any setting that can change this when creating a new database or working with an existing one, or is it necessary to manually modify all scripts with this setting?



Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply