Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: bit of a logic one?

    I need a scheduled job, or a stored proc, or something, that can run against two databases at once. Is this possible?

    I've currently got two databases that are nearly identical in structure. They both run online forums. Each of them runs a SP as a scheduled job step every ten minutes. Both SP's check the same Outlook mailbox (a restriction of SQL Mail), for messages relating to that forum, and processes them if necessary. They check whether a message relates to "their" forum/database by comparing the email's "from" address against the email addresses in the "forum_users" table.

    The problem is with spam and other miscellany that neither of the databases decide is for them. I can't make the SP's run on a "if it's not for me, delete it" basis, because what if it's for the other database?

    So I need a "cleaning" SP - one that will run through and compare the email "from" address against the email addresses in BOTH databases - and then delete anything that doesn't match.

    Any ideas?

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Maybe it's more complex but...

    You can access another database from an SP, by qualifying like this:

    create procedure dbo.multidb as

    select * from table1

    select * from database2.dbo.table2

    GO

  3. #3
    Join Date
    Sep 2002
    Location
    Dallas
    Posts
    7

    Try Union with linked servers

    You could use UNION along with a linked server setup to do something like this:

    IF EXISTS
    (SELECT * FROM
    (
    SELECT * FROM SERVER1.DB1.dbo.forum_users
    UNION
    SELECT * FROM SERVER2.DB1.dbo.forum_users
    ) a
    WHERE a.User = @User
    )
    BEGIN
    ...
    END

    -Dan

Posting Permissions

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