I am using SSDT to create a database containing CLR user defined aggregates.
The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a post-deployment script to move the objects after deployment.
What I am attempting to do is:
- Create the CLR aggregates in a single schema (the “CLR” schema) using the “Default schema” option
- Use a CREATE AGGREGATE wrapper in the desired schema to call the CLR aggregate
For example, I have a CLR aggregate like:
(Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1))
public struct StatsEntropy : IBinarySerialize
This get compiled and becomes CLR.StatsEntropy in the database. The database is called AnalysisFunctions, so it has an assembly also called AnalysisFunctions. This is no problem.
I then create a wrapper in the Stats schema like so:
CREATE AGGREGATE (Stats).(Entropy)
EXTERNAL NAME AnalysisFunctions.StatsEntropy
This works without error when I build via debug. I have my debug connection set to a named instance of SQL Server. If I press F5, it compiles and deploys without any problem. The code is now usable on the server, including the wrapper.
But, if I attempt to publish to a server using Publish, it does not work. Instead it fails with:
Errors occurred while modeling the target database. Deployment cannot continue.
There are no further error messages either in the Data Tools Operations panel or error list. ssdttrace shows no useful information:
This only happens when the database already exists on the server. If I drop the database, it can deploy successfully. I can deploy successfully via debug even if the database already exists, it is only via Publish that the problem occurs. I did not start getting this problem until I added the aggregate wrappers.
Previously I was also getting errors indicating that the assembly AnalysisFunctions had an unresolved reference due to the use of EXTERNAL NAME – again working via debug, not via publish. But now that error has mysteriously vanished.
I am using Visual Studio 2017 15.9.21 and SSDT 15.1.62002.01090, deploying to SQL Server 2019.
Does anyone have any idea what this error might be, or how I can debug it?