Hello all

I have a database and am concatenatiing strings in a view. the value is
coming up as null so I assumed that this was becasue 'concat null yields
null' was set to true. I changed this setting (using the below) and checked
it with the query on the database property (as well as checking the db
option) and these two checks show that everything is fine, but I still
return null. If I create a SP and set this value at runtime then the results
I get are fine. I am using SQL 2005 and my compatibility option is set to
90. Does anyone have any idea as to what is going on?

TIA
Saira

sp_dboption 'databasename', 'concat null yields null', 'false'

USE databasename

SELECT DATABASEPROPERTY('databasename', 'IsNullConcat ')

My guess is that the program/driver you are using to connect to the database
is sending a
set concat_null_yields_null on
when you connect. If you are using QA, you can go to Tools|Options, and the
connections tab, and you will set a check box to specify whether or not you
want to use that option.

When you do a set concat_null_yields_null on, that overrides any value in
the database.

But it seems to me, the simplest and best solution is just to use
Coalesce(<column name>, '') in your view. Then you don't have to worry
about the setting of this option. You don't want to be depending on the
setting of these options since they are deprecated and may be removed in a
future version of SQL Server.

Tom

when you connect. If you run SQL profiler when you connect to the database
you will
"Saira" <Saira@nomail.5DFinance.com> wrote in message
news:ekTZCFB6IHA.2260@TK2MSFTNGP03.phx.gbl...
> Hello all
>
> I have a database and am concatenatiing strings in a view. the value is
> coming up as null so I assumed that this was becasue 'concat null yields
> null' was set to true. I changed this setting (using the below) and
> checked it with the query on the database property (as well as checking
> the db option) and these two checks show that everything is fine, but I
> still return null. If I create a SP and set this value at runtime then the
> results I get are fine. I am using SQL 2005 and my compatibility option is
> set to 90. Does anyone have any idea as to what is going on?
>
> TIA
> Saira
>
> sp_dboption 'databasename', 'concat null yields null', 'false'
>
> USE databasename
>
> SELECT DATABASEPROPERTY('databasename', 'IsNullConcat ')
>
>

Sorry, that got somewhat garbled. It was supposed to be:

My guess is that the program/driver you are using to connect to the database
is sending a
set concat_null_yields_null on
when you connect. If you run SQL profiler when you connect to the database
I think you will see that command being sent. If you are using QA, you can
go to Tools|Options, and the connections tab, and you will set a check box
to specify whether or not you want to use that option.

When you do a set concat_null_yields_null on, that overrides any value of
the 'concat null yields null' option in the database.

But it seems to me, the simplest and best solution is just to use
Coalesce(<column name>, '') in your view. Then you don't have to worry
about the setting of this option. You don't want to be depending on the
setting of these options since they are deprecated and may be removed in a
future version of SQL Server.

Tom

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:uwf0ebB6IHA.1280@TK2MSFTNGP02.phx.gbl...
> My guess is that the program/driver you are using to connect to the
> database is sending a
> set concat_null_yields_null on
> when you connect. If you are using QA, you can go to Tools|Options, and
> the connections tab, and you will set a check box to specify whether or
> not you want to use that option.
>
> When you do a set concat_null_yields_null on, that overrides any value in
> the database.
>
> But it seems to me, the simplest and best solution is just to use
> Coalesce(<column name>, '') in your view. Then you don't have to worry
> about the setting of this option. You don't want to be depending on the
> setting of these options since they are deprecated and may be removed in a
> future version of SQL Server.
>
> Tom
>
> when you connect. If you run SQL profiler when you connect to the
> database you will
> "Saira" <Saira@nomail.5DFinance.com> wrote in message
> news:ekTZCFB6IHA.2260@TK2MSFTNGP03.phx.gbl...
>> Hello all
>>
>> I have a database and am concatenatiing strings in a view. the value is
>> coming up as null so I assumed that this was becasue 'concat null yields
>> null' was set to true. I changed this setting (using the below) and
>> checked it with the query on the database property (as well as checking
>> the db option) and these two checks show that everything is fine, but I
>> still return null. If I create a SP and set this value at runtime then
>> the results I get are fine. I am using SQL 2005 and my compatibility
>> option is set to 90. Does anyone have any idea as to what is going on?
>>
>> TIA
>> Saira
>>
>> sp_dboption 'databasename', 'concat null yields null', 'false'
>>
>> USE databasename
>>
>> SELECT DATABASEPROPERTY('databasename', 'IsNullConcat ')
>>
>>
>
>