sp_dboption 'concat_null_yields_null' not working?

[JCR]

Hi,
We are using SQL 7.00.623.
We are trying to use sp_dboption to set OFF 'concat_null_yields_null' as follows:
sp_dboption @dbname='WWNJ', @optname='concat null yields null', @optvalue='OFF'.
The result is:
Checkpointing database that was changed.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
if we do:
sp_dboption @dbname='WWNJ', @optname='concat null yields null', the result is:
OptionName CurrentSetting
----------------------------------- --------------
concat null yields null off

Yet, we still get Null values in our Query. Now, if we use SET CONCAT_NULL_YIELDS_NULL OFF before the query, it works.

Any Ideas why sp_dboption is not working for us?
thanks in advance,
JCR

 

[tlbroadbent]

From SQL Books Online:

By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to SQL Server.

Therefore, if you are using ODBC or OLE DB to perform your query, the option will set back on. Terry

http://members.home.net/tlbroadbent/index.htm