Is it possible to create SQL CLR dll ?

[pada57]

Hello,

Is it possible to generate like for sqlproj SQL CLR dll out of MSBuild.Sdk.SqlProj project ?

Tried and only dacpac is generated in bin folder. I have set to c# compiler SqlFunctions.

If yes how to proceed and sign/version this dll generated ?

Thanks a lot

[jmezach]

@pada57 Currently we do not support SQLCLR assembly generation as part of the build process and we don't currently have plans to add support for this. To be honest I'm not sure what the plans are for SQLCLR, but I don't think it is being actively developed or maintained.

That being said it is not impossible to add, so I'll leave this issue open for now to gauge interest in this feature.

[pada57]

@jmezach thanks for you quick reply.

SQLCLR is used in my company and still supported in Sql Server 2019 and azure. Don't think will go away soon as support for external language as R, Python have been added recently.

Main benefit i see now for using MSBuild.Sdk.SqlProj is to avoid merge conflicts in rather big team as having small project file.
For most of our projects i will not migrate to it as there are too much contraints at the moment in order of importance i see:

• support for SQL CLR
• schema analysis and schema comparisons
• intellisense + editors
• not all options as db project as for database properties (not most important as can be scripted)

[jmezach]

First of all thank you for your feedback @pada57. To clarify my previous comment I'm not saying that SQLCLR support is going away and I can't really comment on that anyway since I don't work for Microsoft. But I don't see Microsoft investing much into it anymore. I guess it is more a matter of us simply not using it within our company so I don't think we will be adding support for it anytime soon. But if there's someone in the community willing and able to add support for it we'd gladly take a PR for it.

As for the IDE features (schema analysis, schema comparisons, intellisense and editors) the README already states that this a known limitation. We've seen people using MSBuild.Sdk.SqlProj as a companion project to facilitate the build process, but keep around the .sqlproj in order to keep these features. Obviously that means you'd still have the merge conflicts problem. However I don't think we'll be adding any of these features to this project since that would require a significant amount of time and the work needed is also quite different from what we've done here so far.

Note: To get some form of IntelliSense you could run a dotnet publish of your project to deploy it to a local SQL Server instance and then you can connect the SQL editor in Visual Studio to that database. This isn't ideal, but at least gets you part of the way there.

For your last point I am curious to hear what's missing. I'm fairly certain we support everything that the existing .sqlproj format does.

[ErikEJ]

Is it not possible to use globbing with the classic csproj files (to avoid merge conflicts)?

[pada57]

Is it not possible to use globbing with the classic csproj files (to avoid merge conflicts)?

unfortunately you can edit sqlproj files with wildcards but on project reload subfolders and items are added as Include. This is beauty of new csproj files which are based on exclusion rules instead of inclusion as for SSDT.

[pada57]

Agree for intellisense i'm not using it usually a lot in ssdt editor as already other alternatives to get this feature even with SSMS.

For your last point I am curious to hear what's missing. I'm fairly certain we support everything that the existing .sqlproj format does.
What i mean is project properties is using an editor to show all database properties which are quite a long list organise quite well on ssdt project, can be valuable for developers as exploratory place for db props. I'm not using it usually as said as i prefer to control how i set database properties and not let dacpac deployment automatically generate sql for it but rather use pre/post deploy for this kind of thing.

[gfody]

I am here trying to migrate an SSDT project w/CLR to a win11 arm on parallels on m2 environment. I am thinking it's not so important to be able to generate the CLR dll but rather to support adding one as a project or nuget reference to resolve the "has an unresolved reference to object [your clr function]" build errors and perhaps to generate the corresponding dml (though we could always just add the clr specific dml to the sqlproj).

I thought it might be supported at least on windows x86 by adding a project reference to an existing SSDT project containing the CLR but doing this lead to this build error:

error MSB3073: The command "dotnet "~.nuget\packages\msbuild.sdk.sqlproj\2.6.1\Sdk../tools/net7.0/DacpacTool.dll" build -o "obj\Debug\netstandard2.0\DBA2.dacpac" -n "DBA2" -v "1.0.0" -sv Sql150 -i "obj\Debug\netstandard2.0\DBA2.InputFiles.txt" -r "X:\asdfhjkl\bin\Debug\DBA.dll;dbl=|dbv=|srv=;" -dp IncludeCompositeObjects=true " exited with code 1.

is this already a supported scenario? how should I package my CLR to be able to reference it and satisfy these build dependencies?

[jmezach]

@gfody This is currently not supported. If you add a non MSBuild.Sdk.SqlProj project as a reference it will just assume that it is a .dacpac even though it is a .dll and add it as a reference when building. Obviously that won't work. I guess we could treat .dll files differently somehow, but I'm not sure what would be needed to make that work. Since we're not using CLR assemblies it hasn't been a priority for us. If somebody is willing to contribute such a feature we'd be more than happy to have a look at it though.