Author Topic: .NET Bulk Copy - strange error  (Read 42 times)

stanl

  • Pundit
  • *****
  • Posts: 650
.NET Bulk Copy - strange error
« on: November 14, 2017, 02:02:03 pm »
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
#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

  • Tech Support
  • *****
  • Posts: 2185
    • WinBatch
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 650
Re: .NET Bulk Copy - strange error
« Reply #2 on: November 15, 2017, 02:45:53 am »
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

  • Pundit
  • *****
  • Posts: 650
Re: .NET Bulk Copy - strange error
« Reply #3 on: November 15, 2017, 03:22:31 am »
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
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

  • Pundit
  • *****
  • Posts: 650
Re: .NET Bulk Copy - strange error
« Reply #4 on: November 15, 2017, 03:44:26 am »
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.