Help with quering SQL Server

Question

I am planning for monitoring application which will use SQL 2005/08 database. It will monitor old server(s) which are using SQL 2000 db. Tools task is to pick the data from the servers do some statistics and insert the data back into tools own database for reporting purpose.

I am using .NET(C#) as development environment.

What i would really like to do is implement Stored Procedures which will pick the monitoring data from other servers and insert it into the monitoring tools databases table.

Now i would like to know whether its possible to write stored procedure like this ? Or i would have to pick the data from servers in program using one connection and then insert it into tools database using the other connection?

Please let me know if in case you can foresee any complication that i should be aware of.

Also some informative/helpful pointers(links, books) will be helpful.

Thanks all for valuable inputs.

Answer 1

You can do it using stored procedures (assuming both servers can see each other of course), by using Linked Servers.

Once setup, you can query across servers by fully qualifying the object name:

SELECT * FROM [AnotherDbServer].[DatabaseName].[Schema].[TableName]

Answer 2

AdaTheDev's answer is right on, but here are some additional pointers.

Make sure you give the table name an alias if you call out specific columns. Otherwise you will get an error for basically having too many qualifications (4 is the max). Also, if you are linked to an instanced server, make sure you use brackets [ ] in the server name. For example:

SELECT FiscalCalendar.Week, FiscalCalendar.Year
FROM [LinkedServer\Instance].MyDatabase.dbo.FiscalCalendar FiscalCalendar