none
Exception thrown: 'System.Runtime.InteropServices.SEHException' in System.Data.dll

    Question

  • Hi there,

    I am working on a windows application that gets the huge data using odbc data source and pusing it to the sql using sqlbulkcopy . I am having some weird problem.

    Since there is a lot of data i am taking it in some no of rows i.e. 100000 records by looping through . now the problem is when it comes to 11 iteration it gives the following error  and the application is terminated.

    Exception thrown: 'System.Runtime.InteropServices.SEHException' in System.Data.dll
    Exception thrown: 'System.OutOfMemoryException' in DataTrasnfer.exe

    giving more information about it


    Unhandled Exception: System.Runtime.InteropServices.SEHException: External component has thrown an exception.
       at System.Data.Common.UnsafeNativeMethods.SQLFetch(OdbcStatementHandle StatementHandle)
       at System.Data.Odbc.OdbcStatementHandle.Fetch()
       at System.Data.Odbc.OdbcDataReader.Read()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)
       at System.Data.DataTable.Load(IDataReader reader)
       at DataTrasnfer.frm_From_uhh.MakeTable_odbc(OdbcCommand cmd_odbc, String str_Table)
    The program '[428] DataTrasnfer.exe' has exited with code 0 (0x0).
    The program '[428] DataTrasnfer.exe: Program Trace' has exited with code 0 (0x0).


    I have also changed the no of record per loop to 1000 but it is still giving error at 11 iteration only.

    i hae gone through some post and done as they have provided which is as follows

    i have ran the visual studio as administrator. 

    but no luck

    Kindly help

    Monday, March 20, 2017 2:42 PM

All replies

  • Hi friend,

    Thanks for posting here, this forum is for Visual Studio debugger tools using, since your issue is more related to windows application developing, I will move it to corresponding forum for you to get a professional answer.

    Thanks for your understanding.

    Best regards,

    Fletch


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 21, 2017 2:54 AM
  • Hi Kiran,

    The First error you are getting: 'System.Runtime.InteropServices.SEHException' in System.Data.dll

    Because in your code might be throwing a native exception that was uncaught. Check if you add the try/ catch handling for exception.

    Second Error: 'System.OutOfMemoryException' in DataTrasnfer.exe

    Because you might be not have sufficient memory to perform the operation. When your data is get loaded your ram memory might be getting full during the 11th loop operation. See if you could limit down a little bit and error still occurs or not.


    Thanks,
    Sabah Shariq

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

    Tuesday, March 21, 2017 12:13 PM
    Moderator
  • You're running out of memory while reading all the data. Most likely you aren't properly cleaning up your resources. You didn't post any code so it is hard to say but you should ensure that every IDisposable object you reference is wrapped in a using statement.

    Given the stack trace it appears that you are calling DataTable.Load which means you may not have any IDisposable objects. But if you're storing the results of that query into a temp variable and then calling Load again both copies would still be in memory. Simply clearing the variable before calling Load again may resolve the problem.

    But I really think you should consider looking into a better solution. For moving large sets of data across data sources SSIS is a better choice. It can do it more efficiently and can handle large datasets without you having to do anything specifically.  This is especially a good solution if you need to ETL stuff on a regular basis.

    If SSIS is not an option then forego using a DataTable and use a DataReader instead. This will pretty much eliminate your memory issues and will likely be faster than using a DataTable. Under the hood a DT is loaded by reading the data using a DataReader, building the schema and then returning all this as an object. DataSets don't work as well as the data gets larger because they need so much memory. Since you're ETLing data across the wire you probably don't need anything that DTs provide. So skip them and use the underlying DR instead. It'll be faster and only a single row (or batch actually) will be loaded at a time. This will also eliminate your batching code. You simply have a while loop until you're done reading the data.

    Michael Taylor
    http://www.michaeltaylorp3.net

    Tuesday, March 21, 2017 1:56 PM
    Moderator
  • Thanks Sabah Shariq for replying,

    I have done with very smalll amount of data i.e.100 record but still it is throwing the same exception on 11 loop.. I also have try catch block used.


    Tuesday, March 21, 2017 4:57 PM
  • Hi Michael Taylor

    I have disposed all the objects that i can dispose as well as i ahve closed connection objects as well. but still no luck. before this i ahve used datareader and provided it directly to the sqlbulkcopy objects but still it is giving the error.

    Folowing is the code. Kindly help

            Try


                Dim obj_con_sql As New CLKPLibrary.CLKPLibraryVB
                obj_con_sql.g_s_DB_CN_Open()

                Dim cmd_Group As New SqlCommand
                Dim ds_Group As New DataSet
                cmd_Group.CommandText = "select distinct (GroupName), min(StartTime) as StartTime  from  Groups  group by GroupName"
                ds_Group = obj_con_sql.g_f_DB_Select_Query_With_Param(cmd_Group, str_Table)

                Dim cmd_Descriptor As New SqlCommand
                Dim ds_Descriptor As New DataSet
                cmd_Descriptor.CommandText = "select Descriptor from  Points  group by Descriptor order by Descriptor"
                ds_Descriptor = obj_con_sql.g_f_DB_Select_Query_With_Param(cmd_Descriptor, "Points")

                Dim dt_Start As DateTime
                Dim dt_End As DateTime
                Dim bln_first_Row As Boolean = True

                'For Each dr_Group In ds_Group.Tables(0).Rows

                If ds_Group.Tables(0).Rows.Count = 0 Then Exit Function

                obj_con_sql.g_s_DB_CN_Close()
                obj_con_sql = Nothing

                ''If bln_first_Row = True Then
                'dt_Start = ds_Group.Tables(0).Rows(0).Item("StartTime")
                ''dt_End = dr_Group.Item("EndTime")
                'dt_End = DateAdd(DateInterval.Minute, 59, dt_Start)

                ''Else

                ''    dt_End = DateAdd(DateInterval.Minute, 59, dt_Start)

                ''End If

                Dim bln_Exit As Boolean = False


                Dim dr_group As DataRow
                Dim dr_descriptor As DataRow
                Dim int_Increment_Min As Integer = 2880
                Dim dbl_Rows As Double
                Dim int_count As Integer = 0

                For Each dr_group In ds_Group.Tables(0).Rows

                    For Each dr_descriptor In ds_Descriptor.Tables(0).Rows
                        dbl_Rows = 0

                        txt_status.Text = txt_status.Text & vbNewLine & "Transferring data of : " & dr_descriptor.Item("Descriptor") & " Start at :" & Now
                        Application.DoEvents()

                        dt_Start = dr_group.Item("StartTime")
                        'dt_End = dr_Group.Item("EndTime")
                        dt_End = Now ' DateAdd(DateInterval.Minute, int_Increment_Min, dt_Start)
                        bln_Exit = False

                        'Do Until bln_Exit = True
                        Do Until dt_Start > Now

                            int_count = int_count + 1

                            Debug.Print("Iteration : " & int_count)


                            If int_count Mod 2 = 0 Then

                                'Debug.Print(GC.GetTotalMemory(True))

                                Dim pro As Process = Process.GetCurrentProcess()
                                SetProcessWorkingSetSize(pro.Handle, -1, -1)
                                'Debug.Print(GC.GetTotalMemory(True))
                                GC.Collect()
                                'Debug.Print(GC.GetTotalMemory(True))

                            End If

                            If int_count = 10 Then
                                Debug.Assert(True)
                            End If


                            Dim ds As New DataSet
                            Dim cmd As New System.Data.Odbc.OdbcCommand

                            cmd.CommandText = "SELECT   * from " & str_Table & "  where  (Time >= ? And Time < ?)  and  GroupName ='" & dr_group.Item("GroupName") & "' and Descriptor ='" & dr_descriptor.Item("Descriptor") & "'"
                            'cmd.CommandText = "SELECT   * from " & str_Table & "  where  (Time >= ? And Time < ?) "

                            'Debug.Print(cmd.CommandText)

                            cmd.Parameters.Clear()
                            cmd.Parameters.Add(New OdbcParameter("TimeFrom", OdbcType.DateTime)).Value = dt_Start
                            cmd.Parameters.Add(New OdbcParameter("TimeTo", OdbcType.DateTime)).Value = dt_End

                            MakeTable_odbc(cmd, str_Table)

                            'dt_Start = DateAdd(DateInterval.Minute, int_Increment_Min, dt_Start)
                            'dt_End = DateAdd(DateInterval.Minute, int_Increment_Min, dt_Start)
                            dt_Start = DateAdd(DateInterval.Minute, int_Increment_Min, dt_End)


                        Loop

                        txt_status.Text = txt_status.Text & vbNewLine & "Transferring data of : " & dr_descriptor.Item("Descriptor") & " end at :" & Now & " total records : " & dbl_Rows

                    Next
                Next

                'Next
                'Return ds.Tables(0)

            Catch ex As Exception
                err.g_s_Errorhandling(ex, Me.Name, "MakeTable_HistValuesData (" & str_Table & ")", Me, True)
            End Try

        Private Function MakeTable_odbc(ByVal cmd_odbc As OdbcCommand, ByVal str_Table As String) As OdbcDataReader

            Try

                'Dim ds As New DataSet
                'Dim dt As New DataTable


                'Dim obj_con As New CLKPLibrary.CLKPLibraryVBODBC(str_con)

                'obj_con.g_s_DB_CN_Open()

                'Dim ds As New DataSet

                ''where(Time >= ? And Time < ?)

                'ds = obj_con.g_f_DB_Select_Query_With_Param(cmd_odbc, "table")
                'Return ds.Tables(0)

                ' Open a connection to the AdventureWorks database.
                Using connection As OdbcConnection =
               New OdbcConnection(str_con)

                    connection.ConnectionTimeout = 10000
                    connection.Open()

                    ' Create a table with some rows.

                    'creating the data Odbc data adapter and passing the connection and string query
                    cmd_odbc.Connection = connection

                    Dim rd As OdbcDataReader = cmd_odbc.ExecuteReader()
                    'If rd.HasRows = True Then
                    Dim tbl As New DataTable
                        tbl.Load(rd)


                    If tbl.Rows.Count > 0 Then p_s_Bulk_Insert_Sql(str_Table, tbl)
                    'End If

                    tbl.Clear()
                    tbl.Dispose()
                    tbl = Nothing

                    rd.Close()
                    connection.Close()
                    connection.Dispose()



                    rd = Nothing
                    'Return rd
                    'GC.Collect()

                End Using

                'Return ds.Tables(0)

            Catch ex As Exception
                err.g_s_Errorhandling(ex, Me.Name, "MakeTable ()", Me, True)
                Return Nothing

            End Try

        End Function


        Private Sub p_s_Bulk_Insert_Sql(ByRef str_Table As String, ByRef tbl As DataTable)

            Dim connectionString As String = GetConnectionString()

            ' Open a connection to the AdventureWorks database.
            Using connection As SqlConnection =
               New SqlConnection(connectionString)
                connection.Open()
                ' Create a table with some rows.

                Using bulkCopy As SqlBulkCopy =
                  New SqlBulkCopy(connection)

                    bulkCopy.BulkCopyTimeout = 100000
                    bulkCopy.DestinationTableName = str_Table

                    bulkCopy.DestinationTableName = str_Table
                    Try
                        ' Write from the source to the destination.
                        bulkCopy.WriteToServer(tbl)

                    Catch ex As Exception
                        err.g_s_Errorhandling(ex, Me.Name, "p_s_Bulk_Insert_Sql (" & str_Table & ") copying data", Me, True)
                    End Try
                End Using
                connection.Close()
                connection.Dispose()

            End Using








        End Sub


    Tuesday, March 21, 2017 5:02 PM
  • The code you posted is VB code, this is the C# forums. If you are writing your code in VB then we need to move this to the VB forums. VB and C#, while based upon .NET; have different memory behaviors.

    I'm also going to point out that you're forcing a GC collection in the middle of your code. That is bad because you are forcing a GC while you're still referencing objects. This is going to cause more objects to be promoted to the next gen which makes it less likely they will actually be collected.

    Also, your code is not using a Using statement so if any exceptions occur you're going to be leaking resources. But since this is VB and not C# I'd like to move this question to the VB forums where they will be better able to reorganize your code such that these problems can be fixed. It may not resolve your issues though as, again, DataSets are not efficient on memory. You really should be considering using either a DataReader directly or SSIS or equivalent.

    Tuesday, March 21, 2017 5:11 PM
    Moderator