none
Change default SQL Connection object settings in VB.NET application RRS feed

  • Question

  • I'm having some issues with my application and am thinking it has to do with the default settings in the SQLconnection object.  it's changing the settings I have in the database and I want to change them.  Specifically, this is what is being sent to my SQL server:

    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read committed

    I want to turn the ansi settings to 'off'..  Where do I find these settings?

    • Moved by Stanly Fan Wednesday, August 16, 2017 8:06 AM from windows forms
    Friday, August 4, 2017 4:17 PM

All replies

  • Hi ColtsFanMN,

    By my search, I don't find any way to change default SQL connection object setting, so you need to SET ansi_nulls off; manually.

    Public Shared Sub OpenAndSetArithAbort(MyConnection As SqlConnection)
    	Using _Command As SqlCommand = MyConnection.CreateCommand()
    		_Command.CommandType = CommandType.Text
    		_Command.CommandText = "SET ANSI_NULLS OFF;"
    
    		MyConnection.Open()
    
    		_Command.ExecuteNonQuery()
    	End Using
    
    	Return
    End Sub

    Here is the same thread that you can refer to:

    https://dba.stackexchange.com/questions/2500/make-sqlclient-default-to-arithabort-on

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, August 7, 2017 9:50 AM