How to avoid Error "There is already an open DataReader associated with this Command" in MSSql 2000

Question

I am using SqlBulkCopy to copy data to 2 separate table in same database.

obj_Command.CommandText = "Select * from  tmpInvDtlMast where InvNo='111'"



Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(con1)
                        bulkCopy.DestinationTableName = "tmpInvDtlMast"

                        Try

                            bulkCopy.WriteToServer(obj_Command.ExecuteReader())
                            Application.DoEvents()

                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
                        Finally
                            bulkCopy.Close()

                        End Try
                    End Using


                        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(con1)
                            bulkCopy.DestinationTableName = "InvDtlMast"

                            Try

                                bulkCopy.WriteToServer(obj_Command.ExecuteReader())
                                Application.DoEvents()

                            Catch ex As Exception
                                Console.WriteLine(ex.Message)
                            Finally
                                bulkCopy.Close()
                            End Try
                        End Using

In the second Bulk upload this error occurs

There is already an open DataReader associated with this Command

I have tried "MultipleActiveResultSets=true" with my connection string

con1.ConnectionString = "Data Source=" & SqlServer & ";Initial Catalog=" & Database & ";Persist Security Info=True;User ID=" & User & ";Password=" & Pass & ";MultipleActiveResultSets=true"

After googling more solutions I got to know that MultipleActiveResultSets=true not support for MSSql 2000. Is there anything to do to solve this in Sql2000. Please help

Im using vb.net 2008

score 0 · Answer 1 · answered Sep 06 '14 at 12:13

As a workaround try duplicating connection and command:

obj_Command.CommandText = "Select * from  tmpInvDtlMast where InvNo='111'"

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(con1)
    bulkCopy.DestinationTableName = "tmpInvDtlMast"

    Try
        bulkCopy.WriteToServer(obj_Command.ExecuteReader())
        Application.DoEvents()

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        bulkCopy.Close()

    End Try
End Using


Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(**con2**)
    bulkCopy.DestinationTableName = "InvDtlMast"

    Try
        bulkCopy.WriteToServer(**obj_Command2**.ExecuteReader())
        Application.DoEvents()

    Catch ex As Exception
        Console.WriteLine(ex.Message)
    Finally
        bulkCopy.Close()
    End Try
End Using

@SajithaRathnayake please share your solution – Horaciux Sep 06 '14 at 16:39 — Horaciux, Sep 06 '14 at 16:39

score 0 · Accepted Answer · answered Sep 06 '14 at 17:21

I have solved my problem myslf. What I did is remove

obj_Command.ExecuteReader()

with new SqlDataReader instance.

obj_Command.CommandText = "Select * from  tmpInvDtlMast where InvNo='111'"
Dim dr as SqlDataReader()
dr=obj_Command.ExecuteNoneQuery()

               Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(con1)
                    bulkCopy.DestinationTableName = "tmpInvDtlMast"

                    Try

                        bulkCopy.WriteToServer(dr)
                        Application.DoEvents()

                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    Finally
                        dr.Close()

                    End Try
                End Using


                    Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(con1)
                        bulkCopy.DestinationTableName = "InvDtlMast"

                        Try

                            bulkCopy.WriteToServer(dr)
                            Application.DoEvents()

                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
                        Finally
                            dr.Close()
                        End Try
                    End Using

How to avoid Error "There is already an open DataReader associated with this Command" in MSSql 2000

2 Answers2