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.
#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
Not sure if this is relevant but...
https://stackoverflow.com/questions/2209387/sqlbulkcopy-writetoserver-not-reliably-obeying-bulkcopytimeout (https://stackoverflow.com/questions/2209387/sqlbulkcopy-writetoserver-not-reliably-obeying-bulkcopytimeout)
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.
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?
oTable = ObjectClrNew( 'System.Data.DataTable')
oAdapter.Fill(oTable)
oBLK.DestinationTableName = cTbl
Message("Connection Object",oConn)
oBLK.WriteToServer(oTable)
Message("Connection Object- Part 2",oConn)
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.