How to check setting of CONCAT_NULL_YIELDS_NULL

[metaperl]

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?

[metaperl]

On Feb 11, 7:55 pm, metaperl <[e-mail]:

Also, is there info on what the default value of this
variable is?

Found it - to check the current value , you can use SESSIONPROPERTY -
http://msdn2.microsoft.com/en-us/library/ms175001.aspx

[Plamen Ratchev]

You can also check the status of this option in your database using
DATABASEPROPERTYEX:
SELECT DATABASEPROPERTYEX('MyDatabase', 'IsNullConcat')

But since the connection level settings can override the default database
setting, using SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') will give you the
value for the current session.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Plamen Ratchev (Pl****@SQLStudio.com) writes:

You can also check the status of this option in your database using
DATABASEPROPERTYEX:
SELECT DATABASEPROPERTYEX('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_YIELDS_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_YIELDS_NULL on
even if the database setting is off.

But since the connection level settings can override the default
database setting, using SESSIONPROPERTY('CONCAT_NULL_YIELDS_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_IDENTIFIER, the setting is saved with the stored procedure.

Very, very confusing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

Plamen Ratchev (Pl****@SQLStudio.com) writes:

>You can also check the status of this option in your database using
DATABASEPROPERTYEX:
SELECT DATABASEPROPERTYEX('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_YIELDS_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_YIELDS_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_YIELDS_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_IDENTIFIER, 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Plamen Ratchev
http://www.SQLStudio.com

[Erland Sommarskog]

Plamen Ratchev (Pl****@SQLStudio.com) writes:

>The same applies to other ANSI settings. Except that for ANSI_NULL and
QUOTED_IDENTIFIER, 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?

Not any that I have noticed.

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_IDENTIFIER is off by default.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

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
http://www.SQLStudio.com