Testing sp_send_dbmail

Question

Does anyone know if there is a way to setup sql server to send all mail to one specific email address rather than what has been passed into sp_send_dbmail?

I ask as I need to verify what is going to be sent on a system that has customer email addresses and I don't want to actually send the email but rather check the contents.

The customer occasionally go onto this system to test certain things out which may trigger emails. At the moment these email are not sent out to anyone as the system is not setup for mail. I want this to stay the same but instead send all these mails to an internal email address. I don't know all the code that sends emails as there are approx. 3000 stored procedures in this system.

I have been searching on-line but can only find how to setup email.

Answer 1

If you're just looking to verify what will be sent, you can query the msdb.dbo.sysmail_mailitems table:

SELECT  *
FROM    msdb.dbo.sysmail_mailitems

This table holds the recipients, subject, body, format, query, sent_date, from_address, reply_to, and some other fields. I also suspect, though I haven't test, that if you updated that table to a new recipient email address that you could send them to the one address as you've requested above.

Answer 2

1)You can check mail is started or not using GUI.

2)you check email sending using following command.

Select * from msdb.dbo.sysmail_sentitems order by last_mod_date desc enter image description here

3. unable to relay in Exchange Server :- Launch Exchange Server Manager and move to Administrative Groups. Select Administrative Group Name then choose Server>Server Name. Now click on Protocols and Select SMTP.

Right-click on Default SMTP Virtual Server and select Properties

In Access tab, click on Relay>only the list below

Now check the checkbox “Allow all systems to successfully authenticate to relay regardless of the list above” and close. ....check with internal team for permission.