Solved

SQL 2005 Query across multiple servers

Posted on 2009-05-16
6
809 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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