Found out the hard way yesterday that after 2008 SQL Server was abandoning use of OLEDB for bulk inserts and openrowset() scripts I was trying to adapt, These were mainly used for linked servers and my environment no longer supports these. so
INSERT INTO [dbo].[NobelBiz_Rescues] SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Data Source=\\Ralfile01\Telesales Management\DBS\Tracker\Traw.accdb','[Traw]')
when executed as a command object gives an error because it appears to be looking for a linked server. So go to old reliable Powershell
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=SVCDEL");
$cn.Open()
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn
# $bc.DestinationTableName = "dbo.LogicalDisk"
# $bc.WriteToServer($dtable)
$cn
$cn.Close()
as test code on my local system - works
Now try it with WB CLR
ObjectClrOption("useany", "System")
oSVR = objectClrNew("System.Data.SqlClient.SqlConnection","Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=SVCDEL" )
oBLK = objectCLRNew("System.Data.SqlClient.SqlBulkCopy",oSVR)
oSVR.Close()
Exit
and it fails with CLR: Type Name not found. Now at this point all I am trying to do is confirm the .Net objects work so I can abandon my older ADO scripting. Since Powershell works I can probably call it from WB but would prefer a pure WB/CLR solution, as I will have over 50 tables to update.
Which line is the error occurring on?
It may simply be a matter of loading the correct assembly, i.e., "System.Data" instead of "System".
Yep. Simple works. It is so great that WB works with .NET :) :) :)