.NET Bulk Copy - strange error

Started by stanl, November 14, 2017, 02:02:03 PM

Previous topic - Next topic

stanl

The script snippet below works until this line:  oBLK.WriteToServer(oTable)

The error is attached, but basically says the script cannot write because the connection is closed. There is no code to close it and I have a long timeout set for the bulkcopy. The error occurs within seconds. I'm assuming everything else works until the end.
Code (WINBATCH) Select

#Include ".\twlib.wbt"
IntControl(73,1,0,0,0)

cINI=DirScript():"bulk.ini"
If ! FileExist(cINI) Then Terminate(@TRUE,"Cannot Continue","Missing %cINI%"
svr=IniReadPvt("Main","svr",".\SQLEXPRESS",cINI)
cDB=IniReadPvt("Main","db","SVCDEL",cINI)
cSQL=IniReadPvt("Main","sql","",cINI)
cConn=IniReadPvt("Main","conn","",cINI)
cTbl=IniReadPvt("Main","tbl","",cINI)

ObjectClrOption("useany", "System.Data")


oSVR = objectClrNew("System.Data.SqlClient.SqlConnection","Data Source=" :cDB:";Integrated Security=SSPI;Initial Catalog=":svr )
oBLK = objectCLRNew("System.Data.SqlClient.SqlBulkCopy",oSVR)
oBLK.BulkCopyTimeout = 500


oConn = ObjectClrNew( 'System.Data.OleDb.OleDbConnection',cConn)
oConn.Open()

oAdapter = ObjectClrNew( 'System.Data.OleDb.OleDbDataAdapter',cSQL,oConn)

oTable = ObjectClrNew( 'System.Data.DataTable')
oAdapter.Fill(oTable)



oBLK.DestinationTableName = cTbl
oBLK.WriteToServer(oTable)

Exit

:WBERRORHANDLER
oConn=0
oCmd=0
ErrorProcessing(0,1,0,0)
Exit

:CANCEL
Exit



td

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

stanl

I read that along with other posts about bulk copy failures. I'm going to insert some stops in the script, i.e. check or connection object, place a big timeout at the end, and maybe inserting a command object for the SQL. I think there is a 'report back' property for bulk insert so possibly I can check the progress. Problem with these kind of scripts is there is no way you can replicate it unless you come to North Carolina.

stanl

Looks like Time for Plan-B.  I put pauses in the script [below]. The first message showed a valid connection object, but as soon as I closed it I got the error that the connection was closed. Plan-B would be opening up an OLEDB recordset, looping and inserting into the server object. Any thoughts on that approach?

Code (WINBATCH) Select

oTable = ObjectClrNew( 'System.Data.DataTable')
oAdapter.Fill(oTable)



oBLK.DestinationTableName = cTbl

Message("Connection Object",oConn)

oBLK.WriteToServer(oTable)

Message("Connection Object- Part 2",oConn)

stanl

And now for my AHAA! moment.  The error wasn't related to the oConn object, but probably the oSVR object.  So I added oSVR.Open(), now I have to enable remote connections on the server which is a more understandable error.