The code I was given for an SQL CLR project contains one function where the .net implementation is invalid. However I have been able run a query to confirm that there is a function with the name of the invalid one on the server.
Schema name assembly_name assembly_class assembly_method permission_set_desc type_desc
dbo ConvertFromUTC database AppName.Database.Functions ConvertFromUTC UNSAFE_ACCESS CLR_SCALAR_FUNCTION
That's enough information to confirm the deployed version is a scalar function not a table as suspected.
Is there I way I can get the input/output parameters from the version loaded in the database to make sure that the correct version is what was suggested in my previous question and not something else? More generally I'd like to be able to get the same results for all the other CLR functions in the database to see if any of them are mismatches vs the code I was given as well.
There are several different SQLCLR object types and so this would take a few queries. The quickest and easiest way to get the definitions, especially for a one-time operation, is to script out the objects via SQL Server Management Studio (SSMS). You should be able to select all of each particular type in "Object Explorer Details" and script them out in all together.
Or, to do all SQLCLR objects across all types in one shot, go to the general "Generate Scripts":
- Right-click on database in Object Explorer
- Go to "Tasks >" submenu
- Select "Generate Scripts..."
- Choose "Select specific database objects"
- Drill down to the various object types – Stored Procedures, User-Defined Functions, User-Defined Types, and User-Defined Aggregtes – and check the ones that you want to script out
- Click "Next >"
- Choose your preferred method of saving / viewing
- Click "Next >"
- Click "Next >" (again, after reviewing)
- Click "Finish"
For just User-Defined Aggregates (UDA) and scalar User-Defined Functions (UDF), you can use the following query (keep in mind that parameter_id
of 0
is the return type):
SELECT OBJECT_NAME(am.[object_id]) AS [Name], am.*, p.*, t.[name] AS [DataType]
FROM sys.assembly_modules am
INNER JOIN sys.parameters p
ON p.[object_id] = am.[object_id]
INNER JOIN sys.types t
ON t.[user_type_id] = p.[user_type_id]
WHERE OBJECT_NAME(am.[object_id]) = N'{function_name}';