My situation is such,I need to read data from multiple different sql connections and store results in my local server database, in tableA.
Basically Im retriving info on all db physical name in multiple servers and storing them on my machine.I keep getting the following error,and Im just a beginer in coding,therefore not very familiar on how to solve this
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
'Make dynamic connection based on database names saved in results(j)
'results(j)- List all databases on each server
'ip - ip refers to ip address of each server
'DynmConnection is a function that accepts the ip,db name and makes connection.It is located in mySqlconn class.There are no problem here,as it returns State:Open
Dim sqlQuery2 as String
Dim sqlConn2 As New SqlConnection
Dim sqlConn as New SqlConnection 'connect to my local SQL
Dim sqlRead As SqlDataReader = Nothing
Dim dbname As String
sqlQuery2 = "select physical_name from sys.database_files where type =0"
For Each dbname In results
sqlConn2 = mySqlconn.DynmConnection(ip, dbname)
If Not sqlConn2 Is Nothing Then
sqlRead = mySqlconn.DataReader(sqlConn2, sqlQuery2)
While sqlRead.Read()
'MessageBox.Show("physical_name" & sqlRead(0).ToString())
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlQuery2, sqlConn2)
Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
'ERROR RETURNED AT THIS POSITION
sqlConn2.Close()
sqlConn = mySqlconn.Connection()
dataAdapter.Fill(dataSet, "[dbo].[TableA]")
End While
End If
Next
Basically Im retriving info on all db physical name in multiple servers and storing them on my machine.I keep getting the following error,and Im just a beginer in coding,therefore not very familiar on how to solve this
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
'Make dynamic connection based on database names saved in results(j)
'results(j)- List all databases on each server
'ip - ip refers to ip address of each server
'DynmConnection is a function that accepts the ip,db name and makes connection.It is located in mySqlconn class.There are no problem here,as it returns State:Open
Dim sqlQuery2 as String
Dim sqlConn2 As New SqlConnection
Dim sqlConn as New SqlConnection 'connect to my local SQL
Dim sqlRead As SqlDataReader = Nothing
Dim dbname As String
sqlQuery2 = "select physical_name from sys.database_files where type =0"
For Each dbname In results
sqlConn2 = mySqlconn.DynmConnection(ip, dbname)
If Not sqlConn2 Is Nothing Then
sqlRead = mySqlconn.DataReader(sqlConn2, sqlQuery2)
While sqlRead.Read()
'MessageBox.Show("physical_name" & sqlRead(0).ToString())
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlQuery2, sqlConn2)
Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
'ERROR RETURNED AT THIS POSITION
sqlConn2.Close()
sqlConn = mySqlconn.Connection()
dataAdapter.Fill(dataSet, "[dbo].[TableA]")
End While
End If
Next
