Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: How to access a DB B from a stored procedure of DB A

    I am planning on using triggers and stored procedures to store modification and insertion that take place on an SQL Server database A to another (probably remote) database B. How can I access database B (which can be remote) from within a stored procedure in database A?

    Any sample code? I am using SQL Server 7.0


    CREATE TABLE Orders (Ord_ID int IDENTITY, Ord_Priority varchar(10))
    go
    CREATE TRIGGER tr_Orders_INSERT
    ON Orders
    FOR INSERT
    AS
    IF (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High') = 1
    BEGIN

    HEEEELP
    (Add code here that will access another database and insert data in a table)

    END
    go
    INSERT Orders (Ord_Priority) VALUES ('High')

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    To insert data into a table in another db just prefix the table with the db and owner. So to insert into Table tbl owned by dbo in Database DB_B: insert into DB_B.dbo.tbl. If the above is located on a linked server SQLSRV_4 then it would be SQLSRV_4.DB_B.dbo.tbl. I don't have SQL7 on hand but I am sure that the Books On Line covers all of this.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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