SQLBulkCopy with CLR UDT gives "Could not find method 'Read' for type 'MyNamespace.MyType' in assembly 'MyType'"

Question

I have written a SQL Server CLR User-defined type (UDT) in SQL Server 2012. I have been able to access it though SQL test scripts, and have used it as a local variable, defined it in a table, and tested it through Visual Studio and SQL Server Management Studio.

We have an service that uses SQLBulkCopy in a fairly generalized fashion to pick up files that are placed in a directory, then insert their contents to the appropriate table. When I add my UDT as a column in one of those tables, I receive an error from the WriteToServer( DataTable ) invocation.

The UDT column is being passed as a System.String, in the hope that the UDT's Parse() method will be called within SQL Server to convert it to the internal type. I have also tried declaring the UDT class within this client program, and passing the data as the UDT type directly.

In either case I receive this error message (edited to take out my proprietary names)

Could not find method 'Read' for type 'MyNamespace.MyType' in assembly 'MyType'

I have reviewed as many similar questions that I can find about this error message, and they generally refer to the format of the CREATE statement. Also, they generally refer to CLR functions, not CLR types, which are slightly different. This is mine:

CREATE TYPE [dbo].[MyType]
EXTERNAL NAME [MyType].[MyNamespace.MyType]

I suspect this might not be the issue, and that, instead, it has to do with how SQLBulkCopy interacts with a SQLCLR UDT. For this particular combination it's difficult to find any in-depth explanation.

Edit #1 - It is custom serialization.

[Serializable]  
[Microsoft.SqlServer.Server.SqlUserDefinedType( Format.UserDefined, MaxByteSize = -1 )]  
public struct MyType: INullable, IBinarySerialize  

Edit #2 - Execute permission is granted

GRANT EXECUTE 
ON TYPE :: MyType
TO PUBLIC 

Edit #3 - adapted testing code

CREATE TABLE [dbo].[TestMyType]
(
    [SourceMachine]       [varchar](32)  NULL,
    [Output]              MyType NULL
)

and updated by

try
{
    DataTable dataTable = new DataTable( "[TestMyType]" );
    dataTable.Columns.Add( "SourceMachine", typeof( System.String ) );
    dataTable.Columns.Add( "Output", typeof( MyNamespace.MyType ) );

    dataTable.Rows.Add( "Ron1", MyNamespace.MyType.Parse( "This is string 1" ) );
    dataTable.Rows.Add( "Ron2", MyNamespace.MyType.Parse( "This is string 2" ) );
    dataTable.Rows.Add( "Ron3", MyNamespace.MyType.Parse( "This is string 3" ) );

    SqlBulkCopy sqlBulkCopy = new SqlBulkCopy( conn );
    sqlBulkCopy.DestinationTableName = "[TestMyType]";
    sqlBulkCopy.WriteToServer( dataTable );
}
catch ( Exception ex)
{
    System.Diagnostics.Debug.WriteLine(ex.Message);                            
    throw;
}

This gave the same error message that is shown above.

Edit #4 - Eliminate SqlBulkCopy from the issue
I have recreated the issue using a parameterized INSERT. I set it up to pass the UDT object from the client to the server as a parameter that directly uses an instance of the UDT.

string sInsert = "INSERT INTO TestMyType VALUES (?, ?)";
SqlCommand command = new SqlCommand(sInsert, conn);
SqlParameter parm1 = new SqlParameter("SourceMachine", "This is Machine 01");
SqlParameter parm2 = new SqlParameter("Output", MyNamespace.MyType.Parse( "This is INSERT 01" ) );
parm2.UdtTypeName = "MyType";
command.Parameters.Add(parm1);
command.Parameters.Add(parm2);
int nResult = command.ExecuteNonQuery();

giving

A first chance exception of type 'System.Data.SqlClient.SqlException'
    occurred in System.Data.dll
Additional information: Could not find method 'Read' for 
    type 'MyNamespace.MyType' in assembly 'MyType'

Answer 1

SqlBulkCopy should be able to handle SQLCLR UDT's (User-Defined Types) just fine. I have succeeded using both DbDataReader and DataTable methods.

Here is what worked for me:

C# code (I made the "client" a SQLCLR stored procedure)

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class xtra
{

    [SqlProcedure]
    public static void BcpTest(SqlInt32 TheID, SqlString TheConnectionString)
    {
        System.Data.DataTable _DataTable = new System.Data.DataTable();
        _DataTable.Columns.Add("ID", typeof(Int32));
        _DataTable.Columns.Add("SomeDate", typeof(DateTime));
        _DataTable.Columns.Add("SomeData", typeof(Type_HashTable));

        Type_HashTable _Bob = Type_HashTable.Parse(@"testKey=testVal");
        _DataTable.Rows.Add(TheID.Value, DateTime.Now, _Bob);

        _DataTable.Rows.Add(TheID.Value + 1, DateTime.Now,
           Type_HashTable.Parse(@"testKey2=testVal2"));

        SqlBulkCopy _BulkCopy = new SqlBulkCopy(TheConnectionString.Value);
        _BulkCopy.DestinationTableName = "dbo.BulkCopyUDT";

        try
        {
            _BulkCopy.WriteToServer(_DataTable);
        }
        finally
        {
            _BulkCopy.Close();
        }
    }
}

T-SQL code

-- DROP TABLE dbo.BulkCopyUDT;
CREATE TABLE dbo.BulkCopyUDT
(
  ID INT NOT NULL CONSTRAINT [PK_BulkCopyUDT] PRIMARY KEY,
  SomeDate DATETIME,
  SomeData [SQL#].[Type_HashTable]
);
GO

GRANT INSERT, SELECT ON dbo.BulkCopyUDT TO [Public];
GRANT EXECUTE ON TYPE::SQL#.Type_HashTable TO [Public];
GO

CREATE PROCEDURE dbo.SqlBulkCopy_Test
(
    @TheID INT,
    @TheConnectionString NVARCHAR(4000) = 
        N'Data Source=(local); Integrated Security=true; Initial Catalog=my_database;'
)
AS EXTERNAL NAME [my_assembly].[xtra].[BcpTest];
GO

ALTER ASSEMBLY [my_assembly] WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

The Test

EXEC dbo.SqlBulkCopy_Test 1;

SELECT *, SomeData.ToString() FROM dbo.BulkCopyUDT;

EXEC dbo.SqlBulkCopy_Test 3,
       N'Data Source=(local); User=test; Password=test; Initial Catalog=my_database;';

SELECT *, SomeData.ToString() FROM dbo.BulkCopyUDT;

---

I also got this working from a Console App, using both SqlBulkCopy and a parameterized ad hoc query:

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkCopyUDT
{
    class Program
    {
        static void Main(string[] args)
        {
            int _TheID = Int32.Parse(args[0]);

            string _TheConnectionString = 
              @"Data Source=(local); Integrated Security=true; Initial Catalog=my_database;";
            if (args.Length > 1)
            {
                _TheConnectionString = args[1];
            }

            //DataTable _DataTable = new DataTable();
            //_DataTable.Columns.Add("ID", typeof(Int32));
            //_DataTable.Columns.Add("SomeDate", typeof(DateTime));
            //_DataTable.Columns.Add("SomeData", typeof(Type_HashTable));

            //Type_HashTable _Bob = Type_HashTable.Parse(@"testKey=testVal");
            //_DataTable.Rows.Add(_TheID, DateTime.Now, _Bob);

            //_DataTable.Rows.Add(_TheID + 1, DateTime.Now,
            //   Type_HashTable.Parse(@"testKey2=testVal2"));

            //SqlBulkCopy _BulkCopy = new SqlBulkCopy(_TheConnectionString);
            //_BulkCopy.DestinationTableName = "dbo.BulkCopyUDT";

            //try
            //{
            //    _BulkCopy.WriteToServer(_DataTable);
            //}
            //finally
            //{
            //    _BulkCopy.Close();
            //}

            using (SqlConnection _Connection = new SqlConnection(_TheConnectionString))
            {
                using (SqlCommand _Command = _Connection.CreateCommand())
                {
                    _Command.CommandType = CommandType.Text;
                    _Command.CommandText =
                        @"INSERT INTO dbo.BulkCopyUDT (ID, SomeDate, SomeData)
                         VALUES (@MyID, GETDATE(), @MyData);";

                    SqlParameter _ParamMyID = new SqlParameter("@MyID", SqlDbType.Int);
                    _ParamMyID.Value = _TheID;
                    _Command.Parameters.Add(_ParamMyID);

                    SqlParameter _ParamMyData = new SqlParameter("@MyData", SqlDbType.Udt);
                    _ParamMyData.UdtTypeName = "SQL#.Type_HashTable";
                    _ParamMyData.Value = Type_HashTable.Parse(@"testKey3=testVal3");
                    _Command.Parameters.Add(_ParamMyData);

                    _Connection.Open();
                    _Command.ExecuteNonQuery();
                }
            }
        }
    }
}

---

P.S. If sending the data directly to a UDT column, then it needs to be in binary form as that is the only way that SqlBulkCopy transports it, as per the source code.

Answer 2

I used an explicit interface notation on two methods in the UDT, like this.

void IBinarySerialize.Read( BinaryReader r )
{
}

void IBinarySerialize.Write( BinaryWriter w )
{
}

But they had to be defined like this:

public void Read( BinaryReader r )
{
}

public void Write( BinaryWriter w )
{
}

The difference was enough to keep SQL Server from identifying the correct method to use on the UDT during SqlBulkCopy and paramaterized INSERT when passed the complete MyType object.

The issue started when I used Visual Studio to add the stub routines that implemented the IBinarySerialize interface. I right-clicked on the interface name at the top of the struct definition and chose "Implement Interface Explicitly". I should have selected "Implement Interface", to generate the method stubs without the qualifiers.