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

It would seem that you have copied the assembly, otherwise you would not be able to call it and get the error. Now you need to figure what part of the environment it is missing in the new database so that `fileName` ends up being empty. — GSerg, Jan 13 '20 at 19:25
any direction you can give me on how I can figure that out...? — user829982, Jan 13 '20 at 19:30
The way I understand it the source code gets converted to a binary code so there is no where to look anymore unless I create the file from the binary code but I was trying to avoid that. — user829982, Jan 13 '20 at 19:35
not anymore although there is a way to convert it from the binary — user829982, Jan 13 '20 at 19:45
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/205888/discussion-between-user829982-and-gserg). — user829982, Jan 13 '20 at 19:52
You have a SQL CLR assembly, without source code, that sends emails. This is a liability (as you are discovering). Unless this assembly does something special, I suggest you use alternative methods of sending emails. such as SQL Mail — Nick.Mc, Jan 14 '20 at 01:16
If you do not have the source for the assembly, you are in deep sh*t, especially if you have copied it to a new database. You can now potentially introduce rouge code in the db. Anyway, how do you call the proc, and what parameters does the proc take? — Niels Berglund, Jan 14 '20 at 03:46

Solomon Rutzky · Accepted Answer · 2020-01-14T20:19:14.703

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

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

1 Answers1