Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: 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 regards

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...WHY doesn't it work...what's the error..

    And did you use sp_addlinkedsrvlogin?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •