concatenate and NULL

I use the command:
exec sp_dboption 'SAP_SAS','concat null yields null','false'
to set the result of a concat (+) to the string and not to null. It works in
the Query Analyzer, but not in the enterprise manager. SQL Server and Agent
are restarted.
Any idea ?
Joia,
You can also SET CONCAT_NULL_YIELDS_NULL ON/OFF which means that each
connection can control its own behavior. EM (& QA, too) issue several SET
commands to make the environment into the one that they want. So, I suspect
that EM is setting this on for you.
If you want to see the list if settings (which I do not remember off the top
of my head) fire up sql profiler to trace TSQL, then connect with EM and see
the list of settings.
The standard OLE DB / ODBC connection also has several settings hard-coded
into it.
Russell Fields
"joia" <joia@.discussions.microsoft.com> wrote in message
news:B732E540-DD1A-45B5-A8FB-A28CE67F1A4D@.microsoft.com...
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works
in
> the Query Analyzer, but not in the enterprise manager. SQL Server and
Agent
> are restarted.
> Any idea ?
|||joia,
This is session setting, and is set at runtime using SET
CONCAT_NULL_YIELDS_NULL {ON|OFF}. sp_dboption, 'dbname','concat null
yields null' is only provided as a default if the client connection does
not specify it. Both QA and EM, set this and override the default value
when you connect.
So, if you use QA, it will only work if you go to
Tools->Options->Connection Properties. Then untick Set
concat_null_yields_null. Then, every connection you open (regardless of
the default database setting, this option will be off.
I don't think you can set this in EM, I couldn't find it anyway, so I
might be wrong. By default it seems that EM sets this option ON when you
create a new connection to a server from EM as shown in this Profiler trace:
Audit Login
-- network protocol: LPC
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
joia wrote:
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works in
> the Query Analyzer, but not in the enterprise manager. SQL Server and Agent
> are restarted.
> Any idea ?
|||Basically, setting this option through sp_dboption is pretty much worthless,
since it is almost always overridden by the tool when they open a new
connection.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"joia" <joia@.discussions.microsoft.com> wrote in message
news:B732E540-DD1A-45B5-A8FB-A28CE67F1A4D@.microsoft.com...
> I use the command:
> exec sp_dboption 'SAP_SAS','concat null yields null','false'
> to set the result of a concat (+) to the string and not to null. It works
in
> the Query Analyzer, but not in the enterprise manager. SQL Server and
Agent
> are restarted.
> Any idea ?