Back in the loop with SQL Server 2014

Started by stanl, November 14, 2017, 06:40:15 AM

Previous topic - Next topic

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

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
Code (WINBATCH ) Select

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

"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

td

It may simply be a matter of loading the correct assembly, i.e., "System.Data" instead of "System".
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

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