example query for join two tables in two different database
-
I am sorry if this is a redundant question. I have searched messages in the SQL and Visual Basic .net message boards and have not seen complete code example for setting up connections before executing a query which joins two(or more) tables and each table exists in a different database but on the same server. I am aware of the naming convention ServerName.DatabaseName.TableName.Column I am aware of populating datasets in vb.net as exemplified below -------------- Begin populate dataset example ----------------------- Dim DataSetForReport As New DataSet Dim BlueConnection As String Dim BlueSQLConn As SqlConnection Dim BlueQuery As String Dim i, TotalRowsReturned As Integer BlueConnection= "uid=test;password=snow;dtabase=DataBaseBlue; server=Spock" BlueSQLConn = New SqlConnection(BlueConnection) BlueSQLConn .Open() BlueQuery = "Select Table1.ColumnA, Table2.ColumnB from Table1, Table2 where ColumnA=ColumnB" Dim SqlDataAdapterForPopulateDataTable As New SqlDataAdapter(BlueQuery, BlueConnection) i = SqlDataAdapterForPopulateDataTable.Fill(DataSetForReport, "TableOfData") SqlDataAdapterForPopulateDataTable.Dispose() SQLConn.Close() If i > 0 Then 'query returned at least one row TotalRowsReturned = i End If -------------- End populate dataset example ----------------------- Goal: to be able to change BlueQuery variable to refer to two different databases - DataBaseBlue and DataBaseRed like something below... BlueQuery = "Select DatabaseBlue.Table1.ColumnA, DatabaseRed.Table2.ColumnB from DatabaseBlue.Table1, DatabaseRed.Table2 where DatabaseBlue.Table1.ColumnA=DatabaseRed.Table2.ColumnB" If I create another connection like RedConnection = "uid=Red; password=Car; dtabase=DataBaseRed; server=Spock" and use it in an addition SQLConnection declaration Dim RedSQLConn As SqlConnection RedSQLConn = New SqlConnection(RedConnection) RedSQLConn.Open() How do I enable/pass SqlDataAdapterForPopulateDataTable variable two different connections? Or how do I accomplish the "Goal" in another way? Thanks for any suggestions. tanya
-
I am sorry if this is a redundant question. I have searched messages in the SQL and Visual Basic .net message boards and have not seen complete code example for setting up connections before executing a query which joins two(or more) tables and each table exists in a different database but on the same server. I am aware of the naming convention ServerName.DatabaseName.TableName.Column I am aware of populating datasets in vb.net as exemplified below -------------- Begin populate dataset example ----------------------- Dim DataSetForReport As New DataSet Dim BlueConnection As String Dim BlueSQLConn As SqlConnection Dim BlueQuery As String Dim i, TotalRowsReturned As Integer BlueConnection= "uid=test;password=snow;dtabase=DataBaseBlue; server=Spock" BlueSQLConn = New SqlConnection(BlueConnection) BlueSQLConn .Open() BlueQuery = "Select Table1.ColumnA, Table2.ColumnB from Table1, Table2 where ColumnA=ColumnB" Dim SqlDataAdapterForPopulateDataTable As New SqlDataAdapter(BlueQuery, BlueConnection) i = SqlDataAdapterForPopulateDataTable.Fill(DataSetForReport, "TableOfData") SqlDataAdapterForPopulateDataTable.Dispose() SQLConn.Close() If i > 0 Then 'query returned at least one row TotalRowsReturned = i End If -------------- End populate dataset example ----------------------- Goal: to be able to change BlueQuery variable to refer to two different databases - DataBaseBlue and DataBaseRed like something below... BlueQuery = "Select DatabaseBlue.Table1.ColumnA, DatabaseRed.Table2.ColumnB from DatabaseBlue.Table1, DatabaseRed.Table2 where DatabaseBlue.Table1.ColumnA=DatabaseRed.Table2.ColumnB" If I create another connection like RedConnection = "uid=Red; password=Car; dtabase=DataBaseRed; server=Spock" and use it in an addition SQLConnection declaration Dim RedSQLConn As SqlConnection RedSQLConn = New SqlConnection(RedConnection) RedSQLConn.Open() How do I enable/pass SqlDataAdapterForPopulateDataTable variable two different connections? Or how do I accomplish the "Goal" in another way? Thanks for any suggestions. tanya
You can't change the query to work on two different databases at the same time. In theory, you can probably get data from two different databases into the same DataSet using two seperate connections and queries. Just make sure the data ends up in two different DataTables in the DataSet. You should be able to put together a DataRelation object that joins the two tables, do your work on it, then use two the two different DataAdapters and their connections to write those two tables back to the databases if you've made any changes. Sorry, I don't have any examples. I've never done it myself. You can find the docs on DataRelation here[^].
Dave Kreskowiak Microsoft MVP - Visual Basic
-
You can't change the query to work on two different databases at the same time. In theory, you can probably get data from two different databases into the same DataSet using two seperate connections and queries. Just make sure the data ends up in two different DataTables in the DataSet. You should be able to put together a DataRelation object that joins the two tables, do your work on it, then use two the two different DataAdapters and their connections to write those two tables back to the databases if you've made any changes. Sorry, I don't have any examples. I've never done it myself. You can find the docs on DataRelation here[^].
Dave Kreskowiak Microsoft MVP - Visual Basic
Dave, Thanks for taking the time to share your thoughts. I was trying to avoid two separate datatables but if that is the only way, then that is the only way. I know there are good/great technical people at Microsoft. However, I would think this is a common enough situation that a method/tool/wrapper would have been added to be able to handle a single query referencing two or more tables in two different SQL Server databases. My two cents..... tanya
-
Dave, Thanks for taking the time to share your thoughts. I was trying to avoid two separate datatables but if that is the only way, then that is the only way. I know there are good/great technical people at Microsoft. However, I would think this is a common enough situation that a method/tool/wrapper would have been added to be able to handle a single query referencing two or more tables in two different SQL Server databases. My two cents..... tanya
tanya foster wrote:
I would think this is a common enough situation that a method/tool/wrapper would have been added to be able to handle a single query referencing two or more tables in two different SQL Server databases.
It may be possible to do with a stored procedure, but not with any code on the client side that I know of. You might want to post the question in the SQL Forum[^]. There's a guy in there, Colin Angus Mackay, who knows FAR more about SQL Server than I do.
Dave Kreskowiak Microsoft MVP - Visual Basic