• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2102
  • Last Modified:

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

0
printmedia
Asked:
printmedia
1 Solution
 
CodeCruiserCommented:
You have two options

1) Populated data tables from both servers separately and then use DataRelation in dataset to join the two datatables.

http://www.codeproject.com/Articles/18304/Using-Data-Relation-with-Data-Table


2) Add one server as a linked server on the other and then use that to join the two tables
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
0
 
printmediaAuthor Commented:
Thank you! I went the linked server route and it works great.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now