CREATE FUNCTION for "CleanFile" failed because T-SQL and CLR types for return value do not-CodePudding

Been scratching my head with this one for a few hours. I've got a handful of CLR DLLs which work ok, but this most recent is giving me a few headaches. Any insight will be helpful.

I've tried several things, such as various string lengths (including nvarchar(MAX)), plus also checked mapping docs

Error Message:

CREATE FUNCTION for "CleanFile" failed because T-SQL and CLR types for return value do not match.

SQL Command I'm running:

CREATE FUNCTION [dbo].[CleanFile](@filename nvarchar(500), @runmode int)
RETURNS nvarchar(500) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [sqlFileClean].[sqlFileClean].[CleanFile]

C# DLL:

//usings removed for visibility
public class sqlFileClean
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString CleanFile(
            SqlString FileName,
            SqlInt32 RunMode)
    {
        try
        {
           //Code removed here for visibility
        }
        catch (Exception ex)
        {
            // return any unhandled error message
            return ex.Message;
        }
    }

};

CodePudding user response：

I made copy/paste your code into new project in VS 2019, then registered assembly (permission_set=SAFE) on MSSQL 2019 - it works fine (in try block just returned some string).

Ensure that your assembly is created from latest and successfully compiled DLL.

If no success - give more details about what you are doing in try { }

If you need to use NVARCHAR(MAX) - you have to use in C# - SqlChars type instead of SqlString.

CodePudding user response：

For those reading this with same problem, I solved the issue by coping the code into a brand new project.