Solved

SQL 2005 Query across multiple servers

Posted on 2009-05-16
6
806 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

810 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