SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Index Ouf of Range at GetLocaleId on BulkCopy
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TexasAggie
Starting Member

USA
9 Posts

Posted - 12/21/2012 :  09:26:34  Show Profile  Reply with Quote
I am getting the following error itermittently on a SQLBulkCopy. I have search Google high and low, but it looks like I may be the only one to ever get it given the Google result set I received... Any idea why this is happening?

System.IndexOutOfRangeException: Index was outside the bounds of the array.
at System.Data.SqlClient.SqlDataReader.GetLocaleId(Int32 i)
at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at DEER_DAQ.modFunctions.SQLBulkCopyCode()


    Public Sub SQLBulkCopyCode()
        Dim tempReader As SqlDataReader
        Dim BufferRowCount As UInt16

        Try
            If inSQLBulkCopyCode Then Exit Sub
            inSQLBulkCopyCode = True
            While SQLBufferConn.State <> Data.ConnectionState.Open
                PostMessageToStatusTab(vbNewLine & "Lost Connection to SQLBufferConn")
                If ConnectToSQL(2) Then
                    PostMessageToStatusTab(vbNewLine & "Restablished Connection to SQLBufferConn")
                Else
                    PostMessageToStatusTab(vbNewLine & "Unable to Restablish Connection to SQLBufferConn")
                End If
                System.Threading.Thread.Sleep(500)
            End While


            While SQLDataConn.State <> Data.ConnectionState.Open
                PostMessageToStatusTab(vbNewLine & "Lost Connection to SQLDataConn")
                If ConnectToSQL(1) Then
                    PostMessageToStatusTab(vbNewLine & "Restablished Connection to SQLDataConn")
                Else
                    PostMessageToStatusTab(vbNewLine & "Unable to Restablish Connection to SQLDataConn")
                End If
                System.Threading.Thread.Sleep(500)
            End While

            ' Get the number of rows in the buffer table
            SQLBufferReadCmd = New SqlCommand("SELECT COUNT(*) FROM " & TestCell, SQLBufferConn)
            BufferRowCount = SQLBufferReadCmd.ExecuteScalar

            ' Get all rows from the buffer (should be 100)
            SQLBufferReadCmd = New SqlCommand("SELECT * FROM " & TestCell, SQLBufferConn)
            tempReader = SQLBufferReadCmd.ExecuteReader

            If Not SQLBulkCopyFirstRun Then
                Dim TempCount As UInt16
                ' Create bulk copier
                SQLBulkCopier = New SqlBulkCopy(SQLDataConn)
                SQLBulkCopier.DestinationTableName = TestCell

                ' Map column headers properly
                For TempCount = 0 To tempReader.FieldCount - 1
                    SQLBulkCopier.ColumnMappings.Add(tempReader.GetName(TempCount), tempReader.GetName(TempCount))
                    'PostMessageToStatusTab(tempReader.GetName(TempCount))
                Next
                SQLBulkCopyFirstRun = True
            End If

            ' Start the bulk copy
            SQLBulkCopier.BatchSize = BufferRowCount
            SQLBulkCopier.WriteToServer(tempReader)


            ' Delete all data from the buffer table
            SQLBufferReadCmd = New SqlCommand("DELETE FROM " & TestCell, SQLBufferConn)
            SQLBufferReadCmd.ExecuteNonQuery()

            ' Reset the buffer size counter
            SQLBufferSize = 0
        Catch ex As Exception
            SQLBulkCopyError = True
            PostMessageToStatusTab("Error in SQL bulk Copy!" & vbNewLine & vbNewLine & ex.ToString)
            TextMessage("Error in SQL bulk Copy!" & vbNewLine & vbNewLine & ex.ToString)
        Finally
            inSQLBulkCopyCode = False
            tempReader.Close()
        End Try
   End sub

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/21/2012 :  10:00:06  Show Profile  Reply with Quote
I could not 100% follow the logic you are trying to implement with the SQLBulkCopyFirstRun boolean. Is it a class variable that is initialized somewhere else? I suspect the error may have something to do with that variable.

What I would suggest is to start with the code from the Microsoft Example and work your way to implementing the SQLBulkCopyFirstRun logic.
Go to Top of Page

TexasAggie
Starting Member

USA
9 Posts

Posted - 12/21/2012 :  10:17:39  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

I could not 100% follow the logic you are trying to implement with the SQLBulkCopyFirstRun boolean. Is it a class variable that is initialized somewhere else? I suspect the error may have something to do with that variable.

What I would suggest is to start with the code from the Microsoft Example and work your way to implementing the SQLBulkCopyFirstRun logic.



The first run if statement serves only to do column mapping. Since the bulkcopy object instance remains, I don't want to have to map columns every time I do a bulk copy (20 second intervals). I'm buffering data on local machines and then moving it to the server in larger chunks because sending a row of data to the server at 1Hz from up to 10 machines at a time was very inefficient.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000