How to write a query that pulls from different databases and different servers

Question

1.00/5 (2 votes)

See more:

SQL-Server-2012

I have data like the following example:

Server A:
Database- DBO1
Table - names
fields - nameid, name

Server B:
Database- DBO2
Table - Worker
fields - nameid, name

I need to inner join the 2 tables but am not sure how to go about doing it. C# and .Net4 are tagged because the sql code will be in a .net application which is connected to sqlserver database.

For now, if someone can post a very simple way, example included, on how write a query with the above conditions that would be great. A sql server query should suffice (not peoccupied with the C# just yet). I'll do my best to answer questions. Thanks!

Posted 31-Jul-15 7:48am

Member 11820531

Add a Solution

Comments

PIEBALDconsult 31-Jul-15 14:46pm

As solution 1 says, try a linked server, but beware that if it's not set up properly the performance can be dreadful. Consider consulting an expert (I'm not one).

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2015-07-31T08:40:00

Solution 1

You can do that using Linked Server. To find or create a Linked Server go to Server Objects --> Linked Servers.

Now to get data from both db, you can query like-

SQL

SELECT nameid,[name] FROM DBO1.dbo.names
UNION 
SELECT nameid,[name] FROM DBO2.dbo.Worker

Hope, it helps :)

Posted 31-Jul-15 8:40am

Suvendu Shekhar Giri

Comments

Member 11820531 31-Jul-15 16:01pm

I am not a system admin and cannot create my own linked server. Do you know of another to do this? I've also tried using sp_addlinkedserver code in t-sql.

Wendelius · Answer 2 · 2015-07-31T20:05:00

If the databases are located in the same SQL Server instance and the credentials you use have access to both databases, then you can simply define the tables using multi-part identifier. Something like

SQL

SELECT  * 
FROM  DBO1.dbo.Names a
INNER JOIN DBO2.dbo.Worker b ON a.NameID = b.NameID;

If you need to access another instance of SQL Server, linked server would be the easiest solution but of that's not possible, then you can perhaps use OPENROWSET[^]. Something like

SQL

SELECT  *
FROM  DBO1.dbo.Names a
INNER JOIN OPENROWSET('SQLNCLI', 
              'Server=AnotherServer;Trusted_Connection=yes;',
              'SELECT NameID, Name
               FROM DBO2.dbo.Worker') b 
      ON a.NameID = b.NameID;