sql server setting concat_null_yields_null

[touf]

I'm migrating from ms-accss to sql server 2005 for a vb.net application.
the problem is that the queries like "select field1+field2 from table"
arereturning NULL if one of the fields is null.
I like to have the concatenation containing the not null values.

I've tried to set the variable concat_null_yields_null to OFF inside the
server management studio , this is giving me a ggod result inside sql server
management studio, but not inside my application.

My connection string is
connectStr = "Provider=sqloledb;Data Source=xxxxxxx;Initial
Catalog=xxxx;Integrated Security=SSPI;"

Is this because I'm using OLEDB?? what should be the solution?
thanks.

 

[William Vaughn]

This is not an OLE DB issue--it's a TSQL issue and it's by design. NULL +
anything is NULL and always has been.
You're going to have to use another strategy.
Incidentally, you should be using SqlClient to access SQL Server--not OleDb.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Patrice]

As this is the usual behavior I would avoid altering this (in particular
nothe that the documentation mention that this feature will be removed in a
future version).

You could :
- change the expression so that if a value is null you take whatever else
best fit (see ISNULL or COALESCE functions)
- see if having NULL rather than a default value is meaningfull for your
application elsewhere. If not you could just make this field NOT NULL.