Back in the loop with SQL Server 2014

[stanl]

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

Code Select Expand


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

Code Select Expand


$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

Code (WINBATCH ) Select Expand


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.

[td]

Which line is the error occurring on?

[td]

It may simply be a matter of loading the correct assembly, i.e., "System.Data" instead of "System".

[stanl]

Yep. Simple works.  It is so great that WB works with .NET  :) :) :)