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

i have written sql server clr user-defined type (udt) in sql server 2012. have been able access though sql test scripts, , have used local variable, defined in table, , tested through visual studio , sql server management studio.

we have service uses sqlbulkcopy in generalized fashion pick files placed in directory, insert contents appropriate table. when add udt column in 1 of tables, receive error writetoserver( datatable ) invocation.

the udt column being passed system.string, in hope udt's parse() method called within sql server convert internal type. have tried declaring udt class within client program, , passing data udt type directly.

in either case receive error message (edited take out proprietary names)

could not find method 'read' type 'mynamespace.mytype' in assembly 'mytype'

i have reviewed many similar questions can find error message, , refer format of create statement. also, refer clr functions, not clr types, different. mine:

create type [dbo].[mytype]
external name [mytype].[mynamespace.mytype]

i suspect might not issue, , that, instead, has how sqlbulkcopy interacts sqlclr udt. particular combination it's difficult find in-depth explanation.

edit #1 - custom serialization.

[serializable]   [microsoft.sqlserver.server.sqluserdefinedtype( format.userdefined, maxbytesize = -1 )]   public struct mytype: inullable, ibinaryserialize   

edit #2 - execute permission granted

grant execute  on type :: mytype 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 string 1" ) );     datatable.rows.add( "ron2", mynamespace.mytype.parse( "this string 2" ) );     datatable.rows.add( "ron3", mynamespace.mytype.parse( "this 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 same error message shown above.

edit #4 - eliminate sqlbulkcopy issue
have recreated issue using parameterized insert. set pass udt object client server parameter directly uses instance of udt.

string sinsert = "insert testmytype values (?, ?)"; sqlcommand command = new sqlcommand(sinsert, conn); sqlparameter parm1 = new sqlparameter("sourcemachine", "this machine 01"); sqlparameter parm2 = new sqlparameter("output", mynamespace.mytype.parse( "this 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: not find method 'read'      type 'mynamespace.mytype' in assembly 'mytype' 

sqlbulkcopy should able handle sqlclr udt's (user-defined types) fine. have succeeded using both dbdatareader , datatable methods.

here worked me:

c# code (i made "client" 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);         }                 {             _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 [public]; grant execute on type::sql#.type_hashtable [public]; go  create procedure dbo.sqlbulkcopy_test (     @theid int,     @theconnectionstring nvarchar(4000) =          n'data source=(local); integrated security=true; initial catalog=my_database;' ) external name [my_assembly].[xtra].[bcptest]; go  alter assembly [my_assembly] permission_set = external_access; go 

the test

exec dbo.sqlbulkcopy_test 1;  select *, somedata.tostring() dbo.bulkcopyudt;  exec dbo.sqlbulkcopy_test 3,        n'data source=(local); user=test; password=test; initial catalog=my_database;';  select *, somedata.tostring() dbo.bulkcopyudt; 

---

i got working console app, using both sqlbulkcopy , 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 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 data directly udt column, needs in binary form way sqlbulkcopy transports it, per source code.