Concatenatation with NULL

I am changing the setting od my database to put concatenate null yields null to off.....the following are the statements i run....

exec sp_dboption 'Solumina','concat null yields null','false'

SELECT 'abc' + NULL

I expect 'abc' to be returned after this....But not so..I get NULL

Can any one tell me this setting has to be changed at the connection level. if so, why has this been provided as a db option.

the following works....

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT 'abc' + NULL

abc

-

ODBC and SQL Query Analyzer will turn this ON by default so you need to explicitly turn the behavior OFF if you are using either of these connection mechanisms

Run this: select databaseproperty(''Solumina', 'IsNullConcat')

is the result 0? then it's set to OFF, however you have to change it from QA to make it wok in a query window

go to Tools-->options-->Connection Properties and uncheck set concat_null_yields_null

Denis the SQL Menace

http://sqlservercode.blogspot.com/