SQL Server SQLCLR function receives parameter error after copying from one DB to another

Question

I am trying to copy the send email assembly from one database to another. I clicked on script assembly as create to and created it in the new db.

When I try to send an email with the function in the newer db I get the following error:

The parameter 'fileName' cannot be an empty string. Parameter name: fileName

How can I copy the assembly across databases?

Some details:

• Both DBs are on the same instance
• Both DBs are owned by the same login
• Using SQL Server 2016
• Assembly is marked as UNSAFE in both DBs
• Both DBs have TRUSTWORTHY enabled
• T-SQL wrapper object is a scalar function / UDF
• Function is being called the same way in both DBs

Answer 1

How can I copy the assembly across databases?

So far I am not seeing how this is a SQLCLR issue. You clearly copied the Assembly and the T-SQL wrapper object else you would be getting T-SQL errors instead of a .NET error.

I clicked on script assembly as create to and created it in the new db.

Given that you scripted out the T-SQL wrapper object and you are getting an error related to an input parameter, you might be running into a bug that causes defaults for NVARCHAR parameters to not script out correctly:

SSMS scripting CLR stored procedure NVARCHAR parameter NULL default as N'' (empty string)

Execute the following in both old and new DBs to make sure that all parameter definitions are the same, including any potential default values (paying close attention to rows that have a 1 for [has_default_value]):

SELECT [name], [user_type_id], [max_length], [is_output],
       [has_default_value], [default_value]
FROM   sys.parameters prm
WHERE  prm.[object_id] = OBJECT_ID(N'dbo.ObjectName')
ORDER BY prm.[parameter_id];

If you find any differences, you will need to update your CREATE statement to include the correct default value(s). For example, if you have:

@SomeParam [nvarchar](1 - 4000) = N``

Then you will need to update that part of your T-SQL script to instead be:

@SomeParam [nvarchar](1 - 4000) = NULL

And then re-run the CREATE (you might need to either first DROP the existing T-SQL wrapper object, or change the CREATE to be ALTER).

Please vote for that Feedback bug report that I linked above. Thanks!

For more info on working with SQLCLR in general, please visit: SQLCLR Info