Getting "Cannot find the type nvarchar(MAX)" when adding new CLR user defined function

Question

I have created a new function in an assembly which has been successfully added as a CLR Assembly to my SQL server. Now I am trying to create a SQL user defined function to map to the new method in that assembly. I have other methods in the assembly which have been mapped successfully.

Here is my function

CREATE FUNCTION [dbo].[FromCamelCase](@value [nvarchar(MAX)])
RETURNS [nvarchar(MAX)] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Tools_CLR].[UserDefinedFunctions].[FromCamelCase]
GO

On execute, I am getting this error.

Msg 15151, Level 16, State 1, Procedure FromCamelCase, Line 2
Cannot find the type 'nvarchar(MAX)', because it does not exist or you do not 
have permission.

I have tried changing the type from nvarchar to just varchar. I have also tried changing the size from MAX to 4000 and also just 50.

Here is my C# method, for reference.

[Microsoft.SqlServer.Server.SqlFunction]
public static string FromCamelCase(string val)
{
    if (val == null) return string.Empty;
    
    val = val.Replace("_", "");
    StringBuilder sb = new StringBuilder(val.Length + 10);
    bool first = true;
    char lastChar = '\0';
    
    foreach (char ch in val)
    {
        if (!first && (char.IsUpper(ch) || char.IsDigit(ch) && !char.IsDigit(lastChar)))
            sb.Append(' ');
    
        sb.Append(ch);
        first = false;
        lastChar = ch;
    }
    
    return sb.ToString();
}

Making that change worked when performed for both references. Pls add it an an answer in case future visitors come across this. My other function uses `[datetime2]` quite happily. thank you for helping — Valamas, May 09 '13 at 02:30

score 18 · Accepted Answer · answered May 09 '13 at 02:51

18

Remove the brackets from around the data type name: RETURNS NVARCHAR(MAX). The double brackets mean it's interpreted as a user-defined data type called "NVARCHAR(MAX)" (one word) and quoted because of the parentheses in the name.

answered May 09 '13 at 02:51

Pondlife

15,992
6
37
51

Michael · Answer 2 · 2019-07-18T19:00:15.970

10

If you want to keep brackets for whatever reason, you can do that. Move the closing bracket to exclude the (MAX) like so:

RETURNS [NVARCHAR](MAX)

edited Jul 18 '19 at 19:00

answered Apr 14 '15 at 19:25

Michael

8,362
6
61
88

Getting "Cannot find the type nvarchar(MAX)" when adding new CLR user defined function

2 Answers2