Solved

SQL 2005 Query across multiple servers

Posted on 2009-05-16
6
808 Views
Last Modified: 2012-06-27
I have a question about building a query in SQL 2005 that I hope someone can answer for me. Basically we have a few SQL Servers in our oganisation and we need to create a report to show some difference between 2 of them whilst we undergo a few integration changes.

So basically the first question is:

Can you query from different tables contained within different SQL instances? If so, how is ths done?

One I know if this is the correct way then I can move onto creating my queries and if I need to export the data from one to the other then I need to do this but it would be a lot easier if I could query from multiple instances.

Thanx in advance for any advice
0
Comment
Question by:Steven O'Neill
[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
  • 2
6 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 400 total points
ID: 24403134
like this:
select * from yourserver.databasename.dbo.yourtable
Please note that dbo is the default schema. If the table is in a different schema, please replace it with the corresponding one.
 
0
 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 100 total points
ID: 24403372
You have to define other servers as Linked servers on the server which will be used to query data from multiple instances.

Simply open the SQL Server Management Studio and add new Linked server in Server Objects. You have to provide remote server name and authentication data for each Linked server.
0
 
LVL 42

Expert Comment

by:pcelba
ID: 24403421
If the Linked server name is e.g. ComputerName\ServerName then you have to enclose it to brackets in SQL statements:

SELECT  t1.column1, t2.column2 FROM [ComputerName\ServerName].testdata.dbo.t1 t1
INNER JOIN [ComputerName2\ServerName2].testdata.dbo.t2 t2 ON t1.id = t2.id

You may define not only SQL Servers as Linked servers but other data resources like FoxPro, Access, etc.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 41

Expert Comment

by:ralmada
ID: 24403473
There's no need to create a linked server, specially if they are SQL instances.  Of course you need to connect to the other servers. To do so, you can register them. Please check this link:
http://msdn.microsoft.com/en-us/library/ms188231.aspx
And then query As I indicated previously.
select * from yourserver.databasename.dbo.yourtable
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24403513
If you prefer to create a linked server, or if you need to access data on databases rather than SQL Server (Access, Oracle, etc) then you will need to use OpenQuery or OpenRowset functions to query them. You Cannot query directly like this "select * from yourserver.database.table".
Check these example of linked servers:
http://msdn.microsoft.com/en-us/library/ms188427.aspx
http://msdn.microsoft.com/en-us/library/ms190312(SQL.90).aspx 
0
 
LVL 2

Author Closing Comment

by:Steven O'Neill
ID: 31582248
Thanx very much for all you input guys. Very much appreciated and very well explained
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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