CODEWITHSUNDEEP
sql-server
REZA SALEHI
REZA SALEHI

Reputation: 39

SQL Server query from two databases in asp.net

I have an asp.net application which uses several databases on several servers. Now I need to join two tables from two databases on different servers.

I use two connection strings, what should I write?

query = select studentcode,mobile 
        from learning.dbo.students 
        where stcode in (select stcode from hs.dbo.currentetrm)

Dim ds As New DataSet()
ad.Fill(ds)

GridView1.DataSource = ds
GridView1.DataBind()

Where learning is one database on one server with its own login and hs is another database with another login.

Upvotes: 0

Views: 1700

Answers (4)

Suresh S
Suresh S

Reputation: 16

You have 2 options:

  1. Use linked server so that you can do everything with single Connection String.
  2. Use 2 Connections and bring the data onto the application side (C#). Now that you have both datasets at same place, you can do the required logic applied.

Upvotes: 0

d89761
d89761

Reputation: 1434

You can't do this with a single query without linked servers, but you could query them separately and then filter your first table based on values from the second one in your .NET code.

Upvotes: 1

zebediah49
zebediah49

Reputation: 7611

EDIT: In short, you can but they have to be linked: See Oded's answer.

In short, you can't.

A SQL query is processed and executed by a single SQL server. This means that a single query cannot join two tables on two different servers.

Depending on the situation, it is probably a better idea to import a table from one server to the other, and then do the query on that server. Hence, the question is "Why do you have related data on two separate servers?"

Upvotes: 0

Oded
Oded

Reputation: 498942

You can't do this from within ASP.NET, not with two connection strings.

One way to accomplish this is to setup these databases as linked servers - this will allow you to run the query on one of the servers using one login and perform the join there through the linked server.

Upvotes: 1

Related Questions