|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sql server setting concat_null_yields_nullI'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. 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. -- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- "touf" <tou***@hotmail.com> wrote in message news:%23$AdNICJIHA.4272@TK2MSFTNGP05.phx.gbl... > 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. > 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. -- Patrice "touf" <tou***@hotmail.com> a écrit dans le message de news: %23$AdNICJIHA.4***@TK2MSFTNGP05.phx.gbl...Show quote > 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. >  |
|||||||||||||||||||||||
Other interesting topics