Notification on query result joining two databases 1

[Noonoo]

Hello.

I have a query which joins tables from 2 databases. If this query returns any records I'd like to send an email notification.

I first thought of using a DTS package but it only seems to be possible to execute a task against one connection and therefore one database at a time.

I also thought of scheduling a bit of vbscript to check this and send the email but can't see a way of doing this against more than one database.

Does anyone have any ideas please?

Thanks.

 

[JamesLean]

Something like this should do it:

IF EXISTS (
  SELECT *
  FROM db1..tbl t1 JOIN db2..tbl t2 ON t1.id = t2.id
)
BEGIN
  EXEC master..xp_sendmail 'me@domain.com', 'Data exists!'
END

--James

 

[Noonoo]

Rocking! Thanks. Problem solved.

Should anyone else come across this article and want to find out what else the xp_sendmail stored procedure does you could try

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp.