Solved

SQL 2005 Query across multiple servers

Posted on 2009-05-16
6
804 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
  • 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 41

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 41

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now