Accessing Tables From Another Server

Hello All.

I need your advise on how I could access table from another server.

Let me explain what I am doing. Currently, I have 2 servers running parallel to one another other each loading from the same set of data into its own database in the night.

I would like to write a stored procedure to report the number of records from selected tables from the 2 servers to see if they match in records loaded. Thereafter, email me the results (I have already settle the sending of email part).

Have picked up some tips in this forum to come up with the below statements and it works fine for me: -

use AABBCC (database name)
select Server='Server 009', name, rows, SystemDate=getdate() from sysindexes
where name in ('invoice_line', 'invoice_line_import', 'sales_order_line', 'sales_delivery_line','Cust_dely_reliabity','sales _order_proc_time','AU1016','NZ_Data')
order by name

Result: -
Server Table Name Rows

Server 002 AU1016 0
Server 002 Cust_Dely_Reliabity 5017342
Server 002 invoice_line 4397800
Server 002 invoice_line_import 12120
Server 002 NZ_Data 649745
Server 002 Sales_Delivery_Line 5323875
Server 002 Sales_Order_Line 5356989
Server 002 Sales_order_proc_time 3651362

Instead of running the above scipt or stored procedure from 2 servers and received 2 email (one from server 002 & one from server 009), I would like to access the other server within the same stored procedure.

I have used sp_addlinkedserver (may not be the correct method) and <servername>.<databasename>.dbo.sysindexes (doesn't work)

Please help. Thanks a million.

The desired result could be:

Server 002 ....
Server 002 ....
.
.

Server 009 .... (result from the 2nd server)
Server 009 ....
.
.

Best regardsOK...WHY doesn't it work...what's the error..

And did you use sp_addlinkedsrvlogin?|||Originally posted by Brett Kaiser
OK...WHY doesn't it work...what's the error..

And did you use sp_addlinkedsrvlogin?

Hello Brett.

Thanks for replying. There is no error. I just want to know if I could access tables of another server within the same stored procedures. Instead of running the above script twice and receive 2 emails, I would like to run the script within one SP so I could compare the results in one glance.

Thanks again.