SQLConnection connection properties

[Rob Teixeira]

This feels weird! It's the first question i've posted around here in years


Anyone know if you can set NULL Concatenation Behavior for a SQLConnection?
You seem to be able to set it for an OLEDBConnection, but not for SQLConnection.
This sucks, since there a number of SP's in a particular database that won't
run unless we override the database null_concat_yield_null value. I blame
the DBAs, but...

Thanks,
-Rob

[Bill McCarthy]

Hi Rob,

LOL. SQL can do that to the best of us at times. Don't know about the connection
string, and if the DBAs won't fix the stored procs then you might want to jsut do
an execute non query, eg:

' open the sql connection
' then:

MyCommand.Type = CommandType.Text
MyCommand.Text = "SET CONCAT_NULL_YEILDS_NULL off"
i = MyCommand.ExecuteNonQuery()

' call your other command here


' then clean up such as setting CONCAT_NULL_YEILDS_NULL on
MyCommand.Type = CommandType.Text
MyCommand.Text = "SET CONCAT_NULL_YEILDS_NULL on"
i = MyCommand.ExecuteNonQuery()



Alternatively you could use the sp_dboption proc.



"Rob Teixeira" <RobTeixeira@@msn.com> wrote in message
news:3cbf0e69$1@10.1.10.29...
>
> This feels weird! It's the first question i've posted around here in years
>
>
> Anyone know if you can set NULL Concatenation Behavior for a SQLConnection?
> You seem to be able to set it for an OLEDBConnection, but not for SQLConnection.
> This sucks, since there a number of SP's in a particular database that won't
> run unless we override the database null_concat_yield_null value. I blame
> the DBAs, but...
>
> Thanks,
> -Rob

[Rob Teixeira]

Thanks Bill, works great.

In OLEDB, that's also set by a property on the connection itself (which,
if i'm not mistaken needs to be set before the connection goes live). Looks
like some of those options aren't tied to the connection object in SQLClient.

-Rob

"Bill McCarthy" <bill_mcc@iprimus.com.au> wrote:
>Hi Rob,
>
>LOL. SQL can do that to the best of us at times. Don't know about the connection
>string, and if the DBAs won't fix the stored procs then you might want to
jsut do
>an execute non query, eg:
>
>' open the sql connection
>' then:
>
>MyCommand.Type = CommandType.Text
>MyCommand.Text = "SET CONCAT_NULL_YEILDS_NULL off"
>i = MyCommand.ExecuteNonQuery()
>
>' call your other command here
>
>
>' then clean up such as setting CONCAT_NULL_YEILDS_NULL on
>MyCommand.Type = CommandType.Text
>MyCommand.Text = "SET CONCAT_NULL_YEILDS_NULL on"
>i = MyCommand.ExecuteNonQuery()
>
>
>
>Alternatively you could use the sp_dboption proc.
>
>
>
>"Rob Teixeira" <RobTeixeira@@msn.com> wrote in message
>news:3cbf0e69$1@10.1.10.29...
>>
>> This feels weird! It's the first question i've posted around here in years
>>
>>
>> Anyone know if you can set NULL Concatenation Behavior for a SQLConnection?
>> You seem to be able to set it for an OLEDBConnection, but not for SQLConnection.
>> This sucks, since there a number of SP's in a particular database that
won't
>> run unless we override the database null_concat_yield_null value. I blame
>> the DBAs, but...
>>
>> Thanks,
>> -Rob
>
>