Solved

SQL 2005 Query across multiple servers

Posted on 2009-05-16
6
805 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

16 Experts available now in Live!

Get 1:1 Help Now