A little history: We have successfully (2 years ago) built .NET CLR code for SQL Server, compiled to an assembly, and loaded into a SQL Server 2008 R2 Enterprise database. We then created the TSQL objects to call the CLR code. This has been working without issue since first installation and we have modified it over the years without issue.
Today: We have a need to modify the CLR code. Doing this in Visual Studio causes no issues with compiling to an assembly. The signatures for functions referenced by TSQL have not changed at all (all parameters, parameter types, etc. have not changed). The modifications were to re-factor some of the underlying code in the functions and create a couple new functions (all of which are private static and do not get referenced in TSQL). We are now in SQL Server 2012 Enterprise (the upgrade went off without an issue in mid 2015 and the DB is in 2012 compatibility mode now).
I can successfully drop and re-create the assembly in SQL Server. When re-creating the TSQL objects that rely on the assembly some bind to the CLR function signatures without issue but the one that was re-factored will not re-create. This error message is
Msg 6550, Level 16, State 2, Procedure udfGetCellValueCLR, Line 2
CREATE FUNCTION failed because parameter counts do not match.
The parameters did not change in any way. When I look at the Visual Studio auto-created TSQL code to create TSQL functions and try to create one using that code it also fails with the same message, so I know the TSQL code is correct (not missing a parameter, order, type, etc.).
It feels like something may be going on with the DB upgrade to 2012. I have tried compiling the assembly targeting .NET 3.5 and .NET 4.0 as well as targeting 2008 R2 database and 2012 database (so 4 combinations). The DB itself is using .NET 4.0 since it is in 2012 compatibility mode.
The database does have CLR enabled.
Any thoughts on how to get this working? Google has failed me so far.
Edit
The exact function signature (copied directly out of Visual Studio) is below (yes, a lot has been passed into this instead of having the CLR code query for data which, in our tests, has shown to have worse performance than passing everything in).
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlDouble udfGetCellValueCLR(
int MetricID,
string CSVDimensionList,
string AggregationSQLString,
string DateColumnForAverage,
bool TimeFrameIsAVariance,
bool TimeFrameIsAPlan,
bool IsAnAverage,
bool TimeFrameIsPercentOfTotal,
bool AggregationAllowsPercentOfTotal,
bool PlanAggregationAllowsPercentOfTotal,
bool MetricIsAPercentage,
int GoodDirection,
int PlanMetricID,
string PlanAggregationSQLString,
int StartDateID,
int EndDateID,
bool NumeratorIsAPlan,
bool NumeratorIsAnAverage,
int NumeratorStartDateID,
int NumeratorEndDateID,
string NumeratorDateColumnForAverage,
bool DenominatorIsAPlan,
bool DenominatorIsAnAverage,
int DenominatorStartDateID,
int DenominatorEndDateID,
string DenominatorDateColumnForAverage,
SqlString SpecialAggregation
)
The TSQL CREATE FUNCTION statement is below (this is the one that errors).
CREATE FUNCTION [be].[udfGetCellValueCLR](
@MetricID [int],
@CSVDimensionList [nvarchar](4000),
@AggregationSQLString [nvarchar](4000),
@DateColumnForAverage [nvarchar](4000),
@TimeFrameIsAVariance [bit],
@TimeFrameIsAPlan [bit],
@IsAnAverage [bit],
@TimeFrameIsPercentOfTotal [bit],
@AggregationAllowsPercentOfTotal [bit],
@PlanAggregationAllowsPercentOfTotal [bit],
@MetricIsAPercentage [bit],
@GoodDirection [int],
@PlanMetricID [int],
@PlanAggregationSQLString [nvarchar](4000),
@StartDateID [int],
@EndDateID [int],
@NumeratorIsAPlan [bit],
@NumeratorIsAnAverage [bit],
@NumeratorStartDateID [int],
@NumeratorEndDateID [int],
@NumeratorDateColumnForAverage [nvarchar](4000),
@DenominatorIsAPlan [bit],
@DenominatorIsAnAverage [bit],
@DenominatorStartDateID [int],
@DenominatorEndDateID [int],
@DenominatorDateColumnForAverage [nvarchar](4000),
@SpecialAggregation [nvarchar](4000)
)
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [EMMACustomCode].[UserDefinedFunctions].[udfGetCellValueCLR]
GO
If the parameter list does not match, then there is in fact a difference between the Assembly (the one that has already been loaded into SQL Server) and the CREATE FUNCTION statement.
If you are using Visual Studio / SQL Server Data Tools (SSDT) to generate the Assembly and T-SQL wrapper objects, then you need to be aware that there are two types of SQL scripts that SSDT generates: Create scripts, and Publish / Deploy scripts.
Create scripts assume nothing about the current state of where they will be deployed. They will drop the Target Database if it already exists, then re-create the Database and load all of the objects (Assembly and T-SQL wrapper objects). The "{ProjectName}_Create.sql" script is not always generated. There is usually an option (check-box) on the "Project Settings" tab of "Project Properties" to enable the creation of this script. This script does not get deployed, it is just there for you to grab.
Publish / Deploy scripts are incremental deployments. These are created by SSDT by first examining the current state of the Target Database and only making the changes necessary to bring the Target up to the state of what is in the project (i.e. in the generated .dacpac file).
If you need to make sure that you have everything scripted that you have code for in your project, then make sure to have the "Create script (.sql file)" option enabled in "Project Properties" | "Project Settings", do a Build / Rebuild (no need to do a Publish), and then check the "Build output path" for the active configuration.
If you want just the changes, then you can use the "{ProjectName}.sql" script. However, you need to actually do a Publish (even to dev -- "Start without Debugging") in order to start the process that checks the current state of the Target DB. And even then, if there were no changes, the incremental Publish/Deploy script will not be created.
You don't need Visual Studio to generate the Publish script. You can do it via the command line using the SqlPackage.exe utility (Action would be "Script").