Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

Connection string for 2 databases on different servers in vb.net

Hi all.

I have the following code that populates a datagridview control in Visual Studio 2010. The problem is that my Select statement needs to join 2 tables from different databases and different servers. How can I make this work?

As you can see currently, my connection string only references db1 on server1.

server1, db1
server2, db2

Dim con As New SqlConnection
        Dim cmd As New SqlCommand

        con.ConnectionString = "Data Source=server1;Initial Catalog=db1;Integrated Security=True"

        con.Open()

        cmd.Connection = con
        'cmd.CommandText = "SELECT  MasterItemNumber, Description, SLXID, QuoteDate, UnitCost, QuotedCost, Notes FROM ItemQuote WHERE MasterItemNumber = @MasterItemNo"
        cmd.CommandText = "SELECT  MasterItemNumber, Description, SLXID, QuoteDate, UnitCost, QuotedCost, Notes FROM ItemQuote INNER JOIN db2.sysdba.ACCOUNT ON ItemQuote.SLXID = db2.sysdba.ACCOUNT.ACCOUNTID WHERE MasterItemNumber = @MasterItemNo"

        cmd.Parameters.AddWithValue("@MasterItemNo", txtMasterItemNo.Text)

        Dim sda As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()

        sda.Fill(dt)

        Return dt

        con.Close()

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can connect to 1 server and use OpenRowset from within your query to get data from the second server. Check http://technet.microsoft.com/en-us/library/ms190312.aspx
Avatar of printmedia
printmedia

ASKER

Thank you! I went the linked server route and it works great.