BulkCopy Timeout Error

[dgorka]

So I'm trying to bring from one Server into another. Now I actually have to split the data between two tables, so I'm creating two queries, then running two BulkCopies. My first one works fine (its 3939 rows of data I'm copying), but my second one gets a timeout error. Is there any way to set the command timeout of a bulk copy? I've set it to 0 in my query that I load into my SqlDataReader, but that makes no changes (I didn't think it would but figured it was worth a try). I've tried taking out my first one and just running the second one and it still times out (the second one is trying to copy over 72000 rows of data). Any ideas? Here is my code:

Private Function populateDB() As Boolean
        Try

            Using conn As New SqlConnection(My.Settings.connectionString)

                'open the connection
                conn.Open()

                
                'get data from the source table as a sqldatareader
                Dim MLNLoan As SqlCommand = New SqlCommand("SELECT DISTINCT(loan_number), job_id FROM " & My.Settings.gdbMLN & ".files_from_txt", conn)
                Dim MLNImage As SqlCommand = New SqlCommand("SELECT '\\abtdsys01\processed$\MLN\Scan\GMAC\' + moved_path_filename, document_type, " & _
                                                                                            "loan_number, job_id FROM " & My.Settings.gdbMLN & ".files_from_txt " & _
                                                                                            "WHERE transfer = 1", conn)
                MLNLoan.CommandTimeout = 0
                MLNImage.CommandTimeout = 0

                Dim readerL As SqlDataReader = MLNLoan.ExecuteReader

                'open the destination connection.
                Dim destConn As New SqlConnection(My.Settings.destConnString)
                destConn.Open()

                'xml_loan
                Using bulkCopyLoan As SqlBulkCopy = New SqlBulkCopy(destConn)
                    bulkCopyLoan.DestinationTableName = My.Settings.gdbXML & ".xml_loan"

                    Try
                        'write from the source to the destination
                        bulkCopyLoan.WriteToServer(readerL)
                    Catch ex As Exception
                        MsgBox("Source:" & vbCrLf & ex.Source & vbCrLf & vbCrLf & "Message:" & vbCrLf & ex.Message & vbCrLf & vbCrLf & "StackTrace:" & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error in SQLBulkCopy Loan")
                    Finally
                        'close the sqldatareader. the bulkcopy object
                        'is automatically closed at the end of the 
                        'using block
                        readerL.Close()
                    End Try
                End Using

                Dim readerI As SqlDataReader = MLNImage.ExecuteReader

                'xml_image
                Using bulkCopyImage As SqlBulkCopy = New SqlBulkCopy(destConn)
                    bulkCopyImage.DestinationTableName = My.Settings.gdbXML & ".xml_image"

                    Try
                        'write from the source to the destination
                        bulkCopyImage.WriteToServer(readerI)
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                        MsgBox("Source:" & vbCrLf & ex.Source & vbCrLf & vbCrLf & "Message:" & vbCrLf & ex.Message & vbCrLf & vbCrLf & "StackTrace:" & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error in SQLBulkCopy Image")
                    Finally
                        'close the sqldatareader. the bulkcopy object
                        'is automatically closed at the end of the 
                        'using block
                        readerI.Close()
                    End Try
                End Using

            End Using

            If Not UpdateStatus() Then
                MsgBox("Error Updating Status Table", MsgBoxStyle.Exclamation, "No Status Update")
            End If

            Return True

        Catch ex As Exception
            MsgBox("Source:" & vbCrLf & ex.Source & vbCrLf & vbCrLf & "Message:" & vbCrLf & ex.Message & vbCrLf & vbCrLf & "StackTrace:" & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error in populateDB()")
            Return False
        End Try
    End Function

Most of my code comes from here
Thanks for your time.

 

[cjard]

Can you not just link the two databases together (in sql server management console) and do an INSERT INTO destdb.dbo.Table1 SELECT * FROM sourcedb.dbo.Table1

 

[dgorka]

I was under the impression that you couldn't do that across two servers. But I'll look into it and see if it can be done that way.

 

[cjard]

You expand the External Links node, or soemthing like that... I forget what.. but yeah, any data source accissible via ODBC is accessible by sql server

 

[dgorka]

Looked into it and found that it does infact work. You sir, are a genious. Thank you! You have no idea how much of a headache you have saved me from.