I traced a login to SQL Server (2000 and 2005) and saw a series of SET
commands upon login. One of these commands is

SET CONCAT_NULL_YIELDS_NULL ON

The database server is configured to set this option OFF by default, and my
database is also configured to set this option OFF. So my question is, why
is this option being set ON? And how can I prevent this from happening when
connecting?

Thanks,

Tom

Basically, the session setting doesn't care whatever you set at the database (or server) level,
rendering the database setting pretty useless.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:B4D54DB5-43E9-47FD-8E6D-DDEAE6DC4775@microsoft.com...
>I traced a login to SQL Server (2000 and 2005) and saw a series of SET
> commands upon login. One of these commands is
>
> SET CONCAT_NULL_YIELDS_NULL ON
>
> The database server is configured to set this option OFF by default, and my
> database is also configured to set this option OFF. So my question is, why
> is this option being set ON? And how can I prevent this from happening when
> connecting?
>
> Thanks,
>
> Tom

So what determines the initial session settings, and is there a way to change
them without issuing a whole bunch of "SET" commands every time you connect?

Tom

"Tibor Karaszi" wrote:

> Basically, the session setting doesn't care whatever you set at the database (or server) level,
> rendering the database setting pretty useless.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Tom" <Tom@discussions.microsoft.com> wrote in message
> news:B4D54DB5-43E9-47FD-8E6D-DDEAE6DC4775@microsoft.com...
> >I traced a login to SQL Server (2000 and 2005) and saw a series of SET
> > commands upon login. One of these commands is
> >
> > SET CONCAT_NULL_YIELDS_NULL ON
> >
> > The database server is configured to set this option OFF by default, and my
> > database is also configured to set this option OFF. So my question is, why
> > is this option being set ON? And how can I prevent this from happening when
> > connecting?
> >
> > Thanks,
> >
> > Tom
>

> So what determines the initial session settings,

Whoever developed your API (ADO, ADO.NET, ODBC, OLEDB, JDBC, RDO, etc).


> and is there a way to change
> them without issuing a whole bunch of "SET" commands every time you connect?

That would be a setting at the API level, so you should check the documentation for the API your are
using. Whatever the API does it out-of control for SQL Server. The API connects and initially gets
the default setting (instance/db), but then immediately the API executes a SRT command to override
whatever you had at the instance/db level.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:2E74F847-D2AF-476C-BC12-93DE8878E3EB@microsoft.com...
> So what determines the initial session settings, and is there a way to change
> them without issuing a whole bunch of "SET" commands every time you connect?
>
> Tom
>
> "Tibor Karaszi" wrote:
>
>> Basically, the session setting doesn't care whatever you set at the database (or server) level,
>> rendering the database setting pretty useless.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Tom" <Tom@discussions.microsoft.com> wrote in message
>> news:B4D54DB5-43E9-47FD-8E6D-DDEAE6DC4775@microsoft.com...
>> >I traced a login to SQL Server (2000 and 2005) and saw a series of SET
>> > commands upon login. One of these commands is
>> >
>> > SET CONCAT_NULL_YIELDS_NULL ON
>> >
>> > The database server is configured to set this option OFF by default, and my
>> > database is also configured to set this option OFF. So my question is, why
>> > is this option being set ON? And how can I prevent this from happening when
>> > connecting?
>> >
>> > Thanks,
>> >
>> > Tom
>>