Search code examples
sql-servervisual-studio.net-assemblysql-server-data-toolssqlclr

SQL Server CLR project version number increment

Hope some one can help me figure this out.

Problem: I have a sql CLR project in visual studio 2013. Every thing seems to work as I can install the project on SQLServer and call its stored procedures etc. However I do not know how to control the version number of this assembly when its installed. When I run following command

select * from sys.assemblies

It results in following

name                principal_id    assembly_id  clr_name
MyTestSqlClrProject 1               65612        mytestsqlclrproject, ***version=0.0.0.0***, culture=neutral, publickeytoken=null, processorarchitecture=msil

What I want to know is what do I need to do to control the version number

version=0.0.0.0

So that I can manually increment it or auto increment it whenever a build is successful.. I have tried to change project>Properties>Project Settings> On presented screen clicked on Advance which shows another dialogue box on(Data Tier-Application) this there is a field Version, but apparently changing this value does not have any impact on the version attribute when I get this result from the database. Thanks

Solution

  • The VersionNumber is an Assembly property. It is the [assembly: AssemblyVersion("x.x.x.x")] attribute that is typically stored in the \Properties\AssemblyInfo.cs file of the project.

    You can either edit that file directly, or you can go to:

    Project (menu) -> Project Properties (menu item) -> SQLCLR (tab) -> Assembly Information... (button) -> Assembly Version (fields)

    If you do not have an \Properties\AssemblyInfo.cs file yet, then entering any info into the Assembly Information popup will create that file upon clicking the OK button.

    Please note that there is a bug in SQL Server that prevents the version number from being displayed in the sys.assemblies system catalog view IF the Assembly is not signed:

    "version" incorrectly showing as 0.0.0.0 in sys.assemblies and ASSEMBLYPROPERTY(name, 'CLRName') for unsigned SQLCLR Assemblies

    Signed Assemblies will show the correct version number in sys.assemblies. When using Object Explorer in either SQL Server Management Studio (SSMS) or Visual Studio to look at the Assembly properties, the correct version number is displayed even if the Assembly has not been signed. Still, it usually best to sign Assemblies anyway, so just sign them and you won't have any issues here (especially because MS might never fix it; I did report that bug on 2016-01-28).

    In terms of auto-incrementing the version number, you can use an * in the last position to do that. For example:

    [assembly: AssemblyVersion("1.2.3.*")]

    or:

    [assembly: AssemblyVersion("1.2.*")]

    Beyond signing the Assembly, you should be aware of complications in loading the Assembly into SQL Server starting in SQL Server 2017. SQL Server 2017 introduced a new security feature ("CLR strict security", an advanced option) that is enabled by default and requires that ALL Assemblies, even those marked as SAFE, be signed with either an Asymmetric Key (i.e. strong name) or Certificate and have a Login (based on whatever was used to sign the Assembly) that has the UNSAFE ASSEMBLY permission. For details on how to make this work, with or without Visual Studio / SSDT, please see the following two posts of mine:

    Please avoid the new Trusted Assemblies "feature" as it has many more flaws than benefits, not to mention it being entirely unnecessary in the first place given that existing functionality already handled the situation "Trusted Assemblies" was meant to address. For full details on that and a demo of the proper way to handle existing, unsigned Assemblies, please see: SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment.