Posted - 06/09/2009 : 08:35:17
| sql bulkcopy class problem in data pumping via vb.net
I am using vb.net to connect remote database SQL server 2005. First I estabilish a VPN connection using teamviewer (non commercial version).
Here is my vb code. I use sql bulkcopy class . The code works fine for small file transfer.
connectionstring = "Data Source= x.xx.xx.xxx,1433;Network Library=DBMSSOCN;Initial Catalog=xxxx;USER=xxxxx;PASSWORD=xxxxx;"
connection = New SqlConnection(connectionstring)
MsgBox(" YOU ARE NOW CONNECTED TO SQL SERVER ", MsgBoxStyle.Information, "FAISAL")
Dim i As Long = 0
Dim sr As StreamReader = New StreamReader(Environment.CurrentDirectory & "\TEST.txt ")
Dim line As String = sr.ReadLine()
Dim strArray As String() = line.Split(",")
Dim dt As DataTable = New DataTable()
Dim row As DataRow
For Each s As String In strArray
row = dt.NewRow()
row.ItemArray = line.Split(",")
i = i + 1
line = sr.ReadLine()
Loop While Not line = String.Empty
Dim bc As SqlBulkCopy = New SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, Nothing)
bc.DestinationTableName = "PDDATA2"
bc.BatchSize = dt.Rows.Count
I tried to transfer a text file of 60 mb size which has
1 column and 40 ,00 000 rows. I am getting an error message as follows
"The CLR has been unable to transition from COM context 0x1a3008 to COM context 0x1a3178 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations."
I can't interpret the error, Any idea to fix the problem ?
Thanks in Advance