Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    12

    Unanswered: Accessing two database on the same server in stored procedure

    I'm trying to created stored procedure that access two different databases on the same server.

    Here is the stored procedure:

    create procedure upd_mts_acct_rollup
    AS

    DECLARE /* Program Defined CONSTANTS */
    @FAILURE int,
    @FETCH_EOF int,
    @FETCH_ERR int,
    @SUCCESS int,
    @TRUE int,
    @FALSE int

    SELECT
    @SUCCESS = 0,
    @FETCH_ERR = 1,
    @FETCH_EOF = 2,
    @TRUE = 1,
    @FALSE = 0

    DECLARE
    @dbug char,
    @cnt int ,
    @commit_cntr int,
    @sysdate datetime

    SELECT
    @dbug = 'Y',
    @cnt = 0 ,
    @commit_cntr = 0


    if @dbug='Y'
    begin
    select @sysdate = getdate()
    print "upd_mtsdata 000: Starting %1!", @sysdate
    end

    DECLARE
    @comp CHAR(4),
    @ledgr CHAR(4),
    @acct_no CHAR(8),
    @pl2_name CHAR(40),
    @asof_date datetime

    Declare /* Misc */
    @rcnt int,
    @rerr int,
    @restat int

    select @asof_date = asof_date from Fails.dbo.mts_last_process

    /* ****************************************** *
    * declare cursor *
    * ****************************************** */
    DECLARE curMtsAcct CURSOR
    FOR SELECT distinct rtrim(company), rtrim(ledger_number)
    FROM Fails.dbo.mts_fails_hist
    WHERE asof_date = @asof_date

    OPEN curMtsAcct

    FETCH curMtsAcct
    INTO @comp,
    @ledgr

    WHILE @@SQLSTATUS = 0
    BEGIN
    SELECT @acct_no = ltrim(rtrim(@comp))+ltrim(rtrim(@ledgr))
    print @acct_no

    /********************************************
    * Retrive Pathfinder Info *
    *********************************************/
    select @pl2_name = pl2_name
    from delivery.dbo.am_acct_rollup
    where acct_no = @acct_no
    and fiscal_year = (select max(fiscal_year) from am_acct_rollup where acct_no = @acct_no)
    and dwld_src = 'MTS'

    print @pl2_name

    FETCH curMtsAcct
    INTO @comp,
    @ledgr
    END
    CLOSE curMtsAcct
    DEALLOCATE CURSOR curMtsAcct
    GO

    When I create it it on Fials database, I get the follwoing error:
    Error: am_acct_rollup not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
    (State:42S02, Native Code: D0)

    Can you please tell me what am I doing wrong and how can I use to different databasees on the same in the same stored procedure.

    Thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Are you really sure that am_acct_rollup is located on the database called "delivery", owned by the DBO ?

    execute a

    Code:
    use delivery
    go
    sp_help  am_acct_rollup
    go
    sp_helpuser user_name()
    go

Posting Permissions

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