Types don't match for creating a CLR Stored Procedure

Question

I have a method in an assembly that looks like this:

namespace MyNameSpace
{
  public class MyClass
  {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyMethod(SqlChars myMessage)
    {
       // Stuff here 
    } 
  }
}

I compile that to a dll and load it into my SQL Server like this:

drop assembly MyAssembly
create assembly MyAssemblyfrom 'C:\Scripts\MyAssembly.dll'

That all works fine.

But now I try to run this:

CREATE PROCEDURE MySproc @message nvarchar(max)
AS EXTERNAL NAME MyAssembly.[MyNameSpace.MyClass].MyMethod

When I run that I get this Error:

CREATE PROCEDURE for "MySproc " failed because T-SQL and CLR types for parameter "@myMessage" do not match.

Any ideas how I can resolve this?

I have also Tried these method signatures:

• public static void MyMethod(string myMessage)
• public static void MyMethod([SqlFacet(MaxSize = -1)] string myMessage)

Answer 1

Here is a link showingg the mapping of CLR to SQL Server Data Types

http://msdn.microsoft.com/en-us/library/ms131092.aspx

In your case SQLChars maps to nvarchar

Answer 2

Sql type nvarchar maps to CLR (.Net) type SqlString:

public static void MyMethod(SqlString myMessage)
{
    if (! myMessage.IsNull) 
    {
        String myMessageString = myMessage.ToString();
        // Do stuff
    }
}