Eric, My server is behaving strange in regards to ANSI Nulls. I did an upgrade from 6.5 to 7.0 a couple months ago. Recently, we noticed a problem with some stored procedures we were running. Part of one of the stored procedures is coded as follows:

update inv_sum set options = 0 where options = null

When the procedure runs, the records do not update to 0 as coded above. If the above procedure is chaged to "is null" rather than "=null", it works fine and the records update.

Now, here's what I find strange. When I run this command to determine my ASNI setting:

sp_dboption 'Database Name',nulls

the output states that ANSI nulls is set to off (which is what I expected to see as I chose that when I did the upgrade). The problem is that the stored procedure output is behaving as if ANSI nulls were set to "on". This is a problem because all my older stored procedures have "=null".

What can I do other than changing all my code to fix this problem. Any idea why this would be happening. I'm really stumped as to why this is happening. Any help would be greatly appreciated.

-- Anonymous, September 01, 2000

Answers

Nathan,

There are three places that you have to turn ANSI Nulls off. The database, which you covered. The server, which can be done by right clicking on the server name in Enterprise Manager and selecting properties. (In SQL 2000/7.5 Beta 1 it is on the Connections tab.) The third place is in the Query Analyzer connection. (In SQL 2000/7.5 Beta 1 it is Query/Current Connection Properties pull down.)

Hope this helps,

Eric

-- Anonymous, September 06, 2000