view seems to ignore the 'concat null yields null' option
in [Programming SQL]
|
Prev: Script to generate SQL Agent jobs.
Next: Finding columns in tables with specific default constraints
From: Saira on 17 Jul 2008 09:30 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 ')
From: Tom Cooper on 17 Jul 2008 10:10 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(a)nomail.5DFinance.com> wrote in message news:ekTZCFB6IHA.2260(a)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 ') > >
From: Tom Cooper on 17 Jul 2008 10:19
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(a)comcast.no.spam.please.net> wrote in message news:uwf0ebB6IHA.1280(a)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(a)nomail.5DFinance.com> wrote in message > news:ekTZCFB6IHA.2260(a)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 ') >> >> > > |