I would like to check the setting of this variable in our MS-SQL 2000
database. Also, is there info on what the default value of this
variable is?
database. Also, is there info on what the default value of this
variable is?
-
-
Re: How to check setting of CONCAT_NULL_YIE LDS_NULL
On Feb 11, 7:55 pm, metaperl <metap...@gmail .comwrote:Found it - to check the current value , you can use SESSIONPROPERTY -Also, is there info on what the default value of this
variable is?
-
Re: How to check setting of CONCAT_NULL_YIE LDS_NULL
You can also check the status of this option in your database using
DATABASEPROPERT YEX:
SELECT DATABASEPROPERT YEX('MyDatabase ', 'IsNullConcat')
But since the connection level settings can override the default database
setting, using SESSIONPROPERTY ('CONCAT_NULL_Y IELDS_NULL') will give you the
value for the current session.
HTH,
Plamen Ratchev
Comment
-
Re: How to check setting of CONCAT_NULL_YIE LDS_NULL
Plamen Ratchev (Plamen@SQLStud io.com) writes:But that setting applies only to clients that uses a legacy API. That is,You can also check the status of this option in your database using
DATABASEPROPERT YEX:
SELECT DATABASEPROPERT YEX('MyDatabase ', 'IsNullConcat')
by setting this setting ON, you can force a client that connects with
DB-Library to have CONACT_NULL_YIE LDS_NULL on. Another way to do this is
the server-level configuration option 'user options'.
If you connect with a modern API, you will have CONCAT_NULL_YIE LDS_NULL on
even if the database setting is off.
Yes, that is the one to check.But since the connection level settings can override the default
database setting, using SESSIONPROPERTY ('CONCAT_NULL_Y IELDS_NULL') will
give you the value for the current session.
The same applies to other ANSI settings. Except that for ANSI_NULL and
QUOTED_IDENTIFI ER, the setting is saved with the stored procedure.
Very, very confusing.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
Comment
-
Re: How to check setting of CONCAT_NULL_YIE LDS_NULL
"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9A425EE CFA69DYazorman@ 127.0.0.1...Plamen Ratchev (Plamen@SQLStud io.com) writes:>>You can also check the status of this option in your database using
>DATABASEPROPER TYEX:
>SELECT DATABASEPROPERT YEX('MyDatabase ', 'IsNullConcat')
But that setting applies only to clients that uses a legacy API. That is,
by setting this setting ON, you can force a client that connects with
DB-Library to have CONACT_NULL_YIE LDS_NULL on. Another way to do this is
the server-level configuration option 'user options'.
>
If you connect with a modern API, you will have CONCAT_NULL_YIE LDS_NULL on
even if the database setting is off.
>
Correct. I just recently had this case at a client site where a third-party
app to bridge between DB2 and SQL Server was setting it to OFF, very
confusing until I found out.
>>But since the connection level settings can override the default
>database setting, using SESSIONPROPERTY ('CONCAT_NULL_Y IELDS_NULL') will
>give you the value for the current session.
Yes, that is the one to check.
>
The same applies to other ANSI settings. Except that for ANSI_NULL and
QUOTED_IDENTIFI ER, the setting is saved with the stored procedure.
>
Very, very confusing.
>
Couldn't agree more. I haven't had a chance to look up those in SQL Server
2008. Any chance it will get better?
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Plamen Ratchev
Comment
-
Re: How to check setting of CONCAT_NULL_YIE LDS_NULL
Plamen Ratchev (Plamen@SQLStud io.com) writes:Not any that I have noticed.>>The same applies to other ANSI settings. Except that for ANSI_NULL and
>QUOTED_IDENTIF IER, the setting is saved with the stored procedure.
>>
>Very, very confusing.
Couldn't agree more. I haven't had a chance to look up those in SQL Server
2008. Any chance it will get better?
The version after SQL 2008 will drop support for DB-Library, but I don't
think that will have any further repurcussions, although the database
options will become virtually meaningless (as if they are not already).
Maybe in SQL 2035 they will drop all legacy settings. But Microsoft
has some cleaning up in their own backyard to do. When you connect with
SQLCMD, QUOTED_IDENTIFI ER is off by default.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
Comment
-
Re: How to check setting of CONCAT_NULL_YIE LDS_NULL
I can understand keeping some legacy settings. But perhaps enforcing that
they are available in old compatibility mode only. Otherwise just too many
shops keep pushing it the old way until that moment the inevitable
happens...
Thanks Erland!
Plamen Ratchev
Comment
Comment