2

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.

Dan Is Fiddling By Firelight
  • 5,730
  • 16
  • 72
  • 119

1 Answers1

1

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":

  1. Right-click on database in Object Explorer
  2. Go to "Tasks >" submenu
  3. Select "Generate Scripts..."
  4. Choose "Select specific database objects"
  5. 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
  6. Click "Next >"
  7. Choose your preferred method of saving / viewing
  8. Click "Next >"
  9. Click "Next >" (again, after reviewing)
  10. 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}';
Solomon Rutzky
  • 41,664
  • 6
  • 112
  • 149
  • I was hoping to be able to get the corresponding .net definition not the SQL one, but I should be able to work with what this gives. – Dan Is Fiddling By Firelight Jul 13 '17 at 15:13
  • 1
    You need to be clearer about ".NET definition"...You can certainly include the Assembly in there, but I did not mention it due to figuring that a bunch of hex bytes weren't going to help much. If you want the source code, you would need to export the Assembly as a binary file (no built-in way to do this) and then view it with a disassembler such as ILspy. – Solomon Rutzky Jul 13 '17 at 15:20
  • OK. Without any build in support, what I was hoping for sounds like it would be more trouble than is worth doing. – Dan Is Fiddling By Firelight Jul 13 '17 at 15:24
  • You can write / use a SQLCLR function to export the Assembly bytes into a binary DLL and then use ILspy. You also have the slim possibility that the source code was uploaded along with the binary. Execute `SELECT * FROM sys.assembly_files WHERE assembly_id <> 1 ORDER BY [assembly_id], [file_id];` and if you have any file_ids above 1, then that is usually source code or PDB debug files. If it is source code (you can tell by the name), then that can be converted to NVARCHAR to see the source. Also, I just updated my answer with a basic query to get the info requested for UDAs and scalar UDFs. – Solomon Rutzky Jul 13 '17 at 15:33