none
Changing default SET options forced by .NET RRS feed

  • Question

  • Hi,

    How to change default SET options of .NET SqlClient Data Provider ?

    .NET sets the options which effectively mean 'user options', etc. are completely useless because .NET overrides it.

    (Recommendation to run a SQL to set the SET options explicitly isn't an answer I'm looking for.)

    Thank you

    • Moved by Olaf HelperMVP Friday, November 14, 2014 7:54 PM Moved from "Database Engine" to a more specific forum for a better response.
    Thursday, August 9, 2012 12:34 PM

All replies

  • MD_12,

    You would need to include namespace Microsoft.SqlServer.Management.Smo & Microsoft.SqlServer.Management.Smo.UserOptions. UserOptions Members link has the tables that list the members exposed by the UserOptions type.

    Hope that helps.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Thursday, August 9, 2012 1:31 PM
  • IIRW, connection object library allows you to change those options (Connection string) .

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Thursday, August 9, 2012 1:37 PM
  • What exactly are the options you trying to set?

    Thursday, August 9, 2012 2:53 PM
  • E.g., ARITHABORT

    Thanks

    Thursday, August 9, 2012 5:20 PM
  • What is IIRW ? I can't find anything relevant on Google about it ...
    Thursday, August 9, 2012 5:23 PM
  • Hi anuragsh,

    Thank you for the answer.

    sp_configure 'user options' get overwritten by the explicitly set options. E.g., you won't be able to do these 2 scenarios : 1) change the server level options and DB level options so you have ARITHABORT off, 2) change the server level options and DB level options so you have ARITHABORT on. The driver will always override it no matter what the user options are set to so you can basically achieve only one of the described scenario but it isn't really any achievement because it's just a matter of irony that user options is set to the same value as the driver explicitly set so it looks like it's working.

    Thank you

    Thursday, August 9, 2012 5:30 PM
  • The ARITHABORT setting is a client connection setting.  You would need to set it before running your SQL code.  For example:

    SET ARITHABORT ON;

    SELECT ......

    This setting is not something you should really be using.  It is designed to ignore certain errors which you should catch before running the command, instead of relying on the setting.

    Thursday, August 9, 2012 6:50 PM
  • Hi,

    Thank you but as I wrote that in the initial post : "Recommendation to run a SQL to set the SET options explicitly isn't an answer I'm looking for".

    Do you know if it's possible to do it on the driver level ?

    P.S.: ARITHABORT is just an example ... .

    Thank you


    • Edited by MD_12 Thursday, August 9, 2012 8:03 PM
    Thursday, August 9, 2012 8:02 PM
  • After .Net connects to SQL Server, it runs:

    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 serializable

    To override those settings, you must send the "SET ARITHABORT ON" after opening the connection.  As far as I know there is no way to change the SET commands .Net sends to the SQL Server.  I have looked and looked for this over the years and again just now.

    This question might be better asked on the .Net forums.  If you can change them, many people would like to know.

    Thursday, August 9, 2012 8:38 PM
  • Actually ARITHABORT isn't good example because the .NET sets this option to off. If user options enable it, then it'll be enabled. But options which .NET sets to on can't be disabled.
    Saturday, August 11, 2012 12:13 PM
  • MD_12, can you please be specific?
    Monday, August 13, 2012 8:22 AM
  • I tried the approach of executing "SET ARITHABORT ON" after opening the database connection and confirmed that this approach does indeed work.  I verified this by clearing the proc cache on my development server (never do this in production), calling the stored procedure from .NET code, and checking the set options in the execution plan that was generated as a result of the call.  Thank you for this suggestion.
    Friday, October 31, 2014 6:32 PM