.NET Bulk Copy - strange error

[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 Expand


#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]

Not sure if this is relevant but...

https://stackoverflow.com/questions/2209387/sqlbulkcopy-writetoserver-not-reliably-obeying-bulkcopytimeout

[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 Expand


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.