none
How do we change SQL Settings when using the CLR? RRS feed

  • Question

  • At some point our CLR build failed to deploy. Researching it, it is because we are using indexed columns and need to set

    CONCAT_NULL_YIELDS_NULL

    to on.  Opening up a brand new project and deploying fails for the same reason.  The first thing the deployment script runs is this:

    GO

    SET

    ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER ON;

    SET

    CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT OFF;

    How do we alter these settings using the Visual C# SQL CLR Project?  Picking other server type projects these settings are available to change, but in 3 days of searching the net and trying to alter this, I haven't been able to come up with anything.  I can open the script file Visual Studio creates and manually change this and import our CLR, but this basically stops me from being able to debug the clr as the first thing VS tries to do when you go to debug the project is to build and deploy it (which fails).

    Wednesday, March 26, 2014 3:40 PM

All replies

  • Hi Fred,

    >How do we alter these settings using the Visual C# SQL CLR Project?

    I think you cannot. SQL Server Database project is CLR integration. Among the major benefits of this integration are:

    1. A better programming model.
    2. Improved safety and security.
    3. Ability to define data types and aggregate function.
    4. Streamlined development through a standardized environment.
    5. Potential for improved performance and scalability.

    For more information, see http://technet.microsoft.com/en-us/library/ms131089.aspx.

    Instead of doing this in project, I think you can change SQL settings using T-SQL. Fortunately, we can deploy the assembly using T-SQL. What you should do is to execute the settings statements before deploy statements. For details about how to deploy using T-SQL, refer to http://technet.microsoft.com/en-us/library/ms345099.aspx.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 27, 2014 2:28 AM
    Moderator
  • The SQL server has CONCAT_NULL_YIELDS_NULL set on and this setting is being set in the script that is generated to deploy.  So, it's like the CLR is defaulting it to this setting and not letting us change it and to top it off, if you are using column indexes (which almost every major enterprise database is) you can't deploy the CLR from the Visual Studio IDE.  Heck, Microsoft is deprecating this field and changing the default to ON, so why in the heck is the CLR defaulting this to OFF?

    This makes zero sense to me and seems like a bug.

    Thursday, March 27, 2014 2:57 AM
  • Maybe I wasn't being clear about what is happening and what is needed.

    To simplify this, let's start over and go over the deployment process.

    To deploy, you simply go to Build and then Deploy solution.

    The project builds and then pushes the CLR to the connected database by running a script file it creates.

    The problem is in the script file it creates.  The first few lines of this file calls several settings in SQL, some to on, some to off.  It doesn't matter what is set in SQL, it always uses the same settings to deploy the CLR.  This is the problem.  One of this settings (CONCAT_NULL_YIELDS_NULL) it is setting to OFF when it should be setting it to ON.

    Thursday, March 27, 2014 12:24 PM
  • What makes this even more confusing to me is that it is setting it to OFF in the first place.  The default setting is ON and in the future if you try to set it to OFF it will throw an error, which means no one will EVER be able to deploy the CLR from the VS IDE.  I imagine a TON of error support calls.

    A snippet from the TechNet site:  ..In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

    http://technet.microsoft.com/en-us/library/ms176056.aspx

    Thursday, March 27, 2014 12:30 PM
  • Hi Fred,

    Please post you thread on Microsoft Connect to meet our product term for response. https://connect.microsoft.com/.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 31, 2014 8:14 AM
    Moderator