concat null yields null off does not work

Question

concat null yields null off does not work

joachim.verhagen

Hall of Fame

Points: 3560
More actions
April 8, 2003 at 5:26 am

#357738
I can see no effect for:
```
sp_dboption 'sv', 'concat null yields null', 'off'
```
For both on and off the result is the same.
I tested with:
```
select 'x' + NULL
```
The result is always NULL.
sp_dboption 'sv' does show that the option is there after 'on' and is gone after 'off', but it does not show in the results of the select.
I also tried:
```
alter database sv  set CONCAT_NULL_YIELDS_NULL off with no_wait
```
This has no effect either.
I see this with MSSQL 2000 SP 3 and MSSQL 7 SP4.
```
SET CONCAT_NULL_YIELDS_NULL OFF
```
and
```
SET CONCAT_NULL_YIELDS_NULL ON
```
do work.
Does anybody know why this does not work?
Joachim.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Never used it enough to set at the db level. Looks like there has been some discussion:

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=ex2WiKQB%24GA.252%40cppssbbsa05&rnum=1&prev=/groups%3Fq%3Dconcat%2Bnull%2Byields%2Bnull%2Bsp_dboption%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3Dex2WiKQB%2524GA.252%2540cppssbbsa05%26rnum%3D1

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&threadm=TNYR98127D14%40tonyrogerson.csi.com&rnum=4&prev=/groups%3Fq%3Dconcat%2Bnull%2Byields%2Bnull%2Bsp_dboption%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3DTNYR98127D14%2540tonyrogerson.csi.com%26rnum%3D4

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Keep in mind if using QA that it has a set of connection options set of which concat null is on. Look at Tools/Options and the Connection Properties tab for more.

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 3

That is it. I can now control it in isqlw with the connection properties and in ODBC with 'Use ANSI nulls, paddings and warnings".

Thank you.

Joachim.