sql bulkcopy class problem in data pumping via vb.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

sql bulkcopy class problem in data pumping via vb.

Author

Topic

pfaisalbe
Starting Member

12 Posts

Posted - 2009-06-09 : 08:35:17

sql bulkcopy class problem in data pumping via vb.net
Hi

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)
connection.Open()
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
dt.Columns.Add(New DataColumn())
Next

Do

row = dt.NewRow()
row.ItemArray = line.Split(",")

dt.Rows.Add(row)

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
bc.WriteToServer(dt)
connection.Close()
bc.Close()

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

Faisal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources