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
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()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Thank you! I went the linked server route and it works great.