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?

On Feb 11, 7:55 pm, metaperl <metap... (AT) gmail (DOT) com> wrote:
Found it - to check the current value , you can use SESSIONPROPERTY -
http://msdn2.microsoft.com/en-us/library/ms175001.aspx

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

Plamen Ratchev (Plamen (AT) SQLStudio (DOT) com) writes:
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.

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, esquel (AT) sommarskog (DOT) 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

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote


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.



Couldn't agree more. I haven't had a chance to look up those in SQL Server
2008. Any chance it will get better?



Plamen Ratchev
http://www.SQLStudio.com

Plamen Ratchev (Plamen (AT) SQLStudio (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) 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

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