Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    10

    Unanswered: Stored Procedure: Update DB on one Server from DB in another Server

    We have two SQL Servers that are in the same subnet. They do "talk" with each other and are administrated by the same SQL Server account. I am running a test sproc on our local dev SQL box with two different databases that "mirror" the databases on the separate SQL Server boxes in our Internet/WAN subnet.

    The purpose of the sproc is to update records in one table within one database on one server ([Server01].[dbo].[MasterMailList].[MasterMailList]) from a different table (with the FK of PUserID) in a different database on another server ([Server02].[dbo].[click].[POFFERS]).

    Here is what I have so far:

    (NOTE: Yes, the database names, table names, etc. are different for testing purposes. Please don't laugh as this is is one of the more "complex" sprocs I've written to date, which to you veterans, will automatically show you how much a novice I really am.)

    --------------------------------------------------------------------------------
    CREATE PROCEDURE [dbo].[lsp_update_masterlist]

    /*
    This stored procedure is used to update the MasterMailList table
    from information contained in the POFFERS table.

    Author: XXXXX, 07/29/02
    */

    AS

    DECLARE @puserid int,
    @openemail bit,
    @datestamp datetime,
    @userip varchar(150)

    SELECT
    @puserid = PUserID,
    @datestamp = Datestamp,
    @openemail = openemail,
    @userip = userip
    FROM [MIRRORDEV].[dbo].[click].[POFFERS]

    while (@puserid IS NOT NULL)
    BEGIN
    UPDATE [MIRRORDEV].[dbo].[Mail_Cleanup].[MasterMailList]
    SET
    openemail = @openemail,
    opendate = @datestamp,
    ip = @userip
    WHERE (PUserID = @puserid)
    END
    GO
    --------------------------------------------------------------------------------

    I get the following error message:

    Server: Msg 208, Level 16, State 1, Procedure lsp_update_masterlist, Line 17
    Invalid object name 'dbo.click.POFFERS'.

    Now, I know that the object constructs are incorrect above, but I don't know what I am doing wrong. I have searched the Net for reliable resources on this error message and the only thing I can find is having unsynced DBO accounts between databases and tables. And I did check to make sure that the dbo users are synced between the two databases.

    I guess I am wondering, is there a mechanism for declaring or identifying which server that the table is being called from? And also if anyone can identify other problems with the above sproc.

    Thanks in advance!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    I guess there is incorrect syntax in FROM part - should be SERVER.DATABASE.OWNER.TABLE.


    SELECT
    @puserid = PUserID,
    @datestamp = Datestamp,
    @openemail = openemail,
    @userip = userip
    FROM [MIRRORDEV].[dbo].[click].[POFFERS]

Posting Permissions

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