SQL 2005 Query across multiple servers
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/
And then query As I indicated previously.
select * from yourserver.databasename.db
http://msdn.microsoft.com/
And then query As I indicated previously.
select * from yourserver.databasename.db
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/
http://msdn.microsoft.com/en-us/library/ms190312(SQL.90).aspx
Check these example of linked servers:
http://msdn.microsoft.com/
http://msdn.microsoft.com/en-us/library/ms190312(SQL.90).aspx
ASKER
Thanx very much for all you input guys. Very much appreciated and very well explained
SELECT t1.column1, t2.column2 FROM [ComputerName\ServerName].
INNER JOIN [ComputerName2\ServerName2
You may define not only SQL Servers as Linked servers but other data resources like FoxPro, Access, etc.