Solved

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

Posted on 2014-02-20
3
1,758 Views
Last Modified: 2014-02-20
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
Comment
Question by:printmedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39873973
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39874306
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
 

Author Closing Comment

by:printmedia
ID: 39874569
Thank you! I went the linked server route and it works great.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.net Open video relating to control 2 38
Data organization issue 7 53
"lblTime is not declared" 3 62
MessageBox Appear behind a form in Runtime 1 26
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question