CODEWITHSUNDEEP
sqlvb.netconnection-string
CWilson
CWilson

Reputation: 435

multiple connection strings in single query

In VB, how do I join two statements from separate technology into a single recordset? Say I have 2 statements:

Select name from server1.dbo.table1 where attribute = "something"
select name from server2.dbo.table2 where attribute = "something else"

I can run each of these statements. Say table1 is a flat file, and table2 is from a SQL Server (or whatever, doesn't matter, hopefully). What I want to happen is to join these into table3, which will be created in a third technology (happens to be SAP, but let's ignore that for this question). I would like a statement a little like this:

select table1.name into server3.dbo.table3 from table1 join table2 on table1.name=table2.name

Can I use three separate connection strings in a single query, in VB?

For this project, I could use vb.net, vbscript, even vba/vb6, whatever. If I have to leave VB, it will make connecting with the third technology a little more difficult, but not impossible. On the other hand I am data access interface (ado/dao/rdo/ODBC/OLEDB/etc.) agnostic.

Upvotes: 1

Views: 2811

Answers (2)

Martin Verjans
Martin Verjans

Reputation: 4796

Can I use three separate connection strings in a single query, in VB?

No, you cannot.

Because when you run a Query (in almost every language), you actually pass a String to a driver, which itself is going to query the actual database. That's the purpose of ADO, OLEDB, among others.

So, in a single query, it's not possible.

However, it is possible for you to fill two different Datasets, one for each of your technologies.

When it's done, you can create a third Dataset, which will be a mix of the first two. You build that third dataset "manually".

This is Okay if you only need to read (SELECT) from the databases.

If you want to write in there, you will have to report any change made on the third dataset to the first two, then commit these changes into the databases.

Upvotes: 1

Aldinei Sampaio
Aldinei Sampaio

Reputation: 241

I believe you can't do that directly. Usually, what I do in these situations is to create a temporary table on one of the connections and import all data from the other connection on it. Then I execute a join and move the data to the third connection.

Imports System.Data.SqlClient

Module Module1

Sub Main()
    Dim connectionString1 = "YOUR FIRST CONNECTIONSTRING HERE"
    Dim connectionString2 = "YOUR SECOND CONNECTIONSTRING HERE"

    Using connection1 = New SqlConnection(connectionString1)
        connection1.Open()

        Using command = connection1.CreateCommand()
            command.CommandText = "CREATE TABLE #T (Name VARCHAR(64))"
            command.ExecuteNonQuery()
        End Using

        Using connection2 = New SqlConnection(connectionString2)
            connection2.Open()

            Using command = connection2.CreateCommand()
                command.CommandText = "SELECT Name FROM Table2"
                Using reader = command.ExecuteReader()

                    Using bulkCopy = New SqlBulkCopy(connection1)
                        bulkCopy.DestinationTableName = "#T"
                        bulkCopy.BatchSize = 10000
                        bulkCopy.ColumnMappings.Add(0, "Name")
                        bulkCopy.WriteToServer(reader)
                    End Using
                End Using
            End Using
        End Using

        Using command = connection1.CreateCommand()
            command.CommandText = "SELECT Table1.Name FROM Table1 INNER JOIN #T ON Table1.Name = #T.Name"

            Using reader = command.ExecuteReader()
                ' Do what you want with the joined data here, 
                ' if you like you can BulkCopy it on another connection
            End Using
        End Using
    End Using
End Sub

End Module

Upvotes: 1

Related Questions