"Concatenate Null Yields Null"

Thanks to the good graces of members I have found the COALESECE() FUNCTION,
and it should fix my problem but still, I'm confused.
I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
question and according to what I read in the Documents on Line for Sql Server
2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
Sadly it returns a NULL value. What gives'
--
Ed Warren> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
Current session settings override default settings specified at the database
or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> Thanks to the good graces of members I have found the COALESECE()
> FUNCTION,
> and it should fix my problem but still, I'm confused.
> I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> question and according to what I read in the Documents on Line for Sql
> Server
> 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> Sadly it returns a NULL value. What gives'
> --
> Ed Warren|||Thanks for the enlightenment. That's what is happening. Now how does one
set the value for an (OLe DB session). From the caller or can this be done
on the server side (by setting options for the user', session). I must
admit I'm new to SQL server 2005 and have overlooked many of the 'gottchas'
Again, thanks.
--
Ed Warren
"Dan Guzman" wrote:
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> Current session settings override default settings specified at the database
> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default when
> connecting via OLE DB or ODBC. This includes SQL Server Management Studio.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
> > Thanks to the good graces of members I have found the COALESECE()
> > FUNCTION,
> > and it should fix my problem but still, I'm confused.
> >
> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
> > question and according to what I read in the Documents on Line for Sql
> > Server
> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
> >
> > Sadly it returns a NULL value. What gives'
> >
> > --
> > Ed Warren
>|||You'll need to set SET CONCAT_NULL_YIELDS_NULL OFF from the client
application after connecting using ODBC/OLE DB. It can be set on the server
side for the duration of a stored procedure execute by including the SET
statement in the proc. However, as you hinted in your original message, the
proper way to handle NULLs is with COALESCE, et. al.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
news:B59289CA-EA9A-413A-9290-9D12D2C772D0@.microsoft.com...
> Thanks for the enlightenment. That's what is happening. Now how does one
> set the value for an (OLe DB session). From the caller or can this be
> done
> on the server side (by setting options for the user', session). I must
> admit I'm new to SQL server 2005 and have overlooked many of the
> 'gottchas'
> Again, thanks.
> --
> Ed Warren
>
> "Dan Guzman" wrote:
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> Current session settings override default settings specified at the
>> database
>> or instance level. CONCAT_NULL_YIELDS_NULL ON is turned on by default
>> when
>> connecting via OLE DB or ODBC. This includes SQL Server Management
>> Studio.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ed Warren" <EdWarren@.discussions.microsoft.com> wrote in message
>> news:3CFDF4E6-0E54-480B-A274-4C7ADDF15425@.microsoft.com...
>> > Thanks to the good graces of members I have found the COALESECE()
>> > FUNCTION,
>> > and it should fix my problem but still, I'm confused.
>> >
>> > I have set the CONCATENATE NULL YIELDS NULL FALSE for the database in
>> > question and according to what I read in the Documents on Line for Sql
>> > Server
>> > 2005, this should return ('ABC') when I concatenate 'ABC'+ NUll.
>> >
>> > Sadly it returns a NULL value. What gives'
>> >
>> > --
>> > Ed Warren