4

I have just taken over responsibility for a SQL 2000 system which made heavy use of xp_sendmail.

We are in the process of upgrading to SQL Server 2008 R2 and I have implemented sp_send_dbmail.

We are currently performing user acceptance testing on the new SQL Server. The application sends out many emails directly to our customers. Since our test database is a copy of our live database, there are many valid customer email addresses in our test database. Is there a way to either temporarily redirect the emails or shut down the Database Mail service, without impacting our testing?

Improve this question

4 Answers 4

Reset to default
7 
USE msdb
GO
EXECUTE dbo.sysmail_stop_sp
GO

Will stop the server from sending mail. EXECUTE dbo.sysmail_start_sp will start up the service again.

Improve this answer
2
  • Perfect. This is exactly what I was looking for, but could'nt seem to find it. Thanks
    – Paul
    Commented Jan 17, 2012 at 4:11
  • 3
    @Paul - Looks like sp_send_dbmail raises a severity 16 error if you've stopped DBMail in this fashion. Depending on how your application is coded to handle errors, that could pose a problem.
    – db2
    Commented Jan 17, 2012 at 13:53
3

Assuming you're sending the messages from within a few stored procedures, I'd recommend checking the database name before sending the message, and skipping that step if it's not the production database. It usually looks something like this:

IF DB_NAME() = 'production_db_name'
    --Do stuff, like sending mail
END IF

Then it will automatically skip the mailing if you're running it from the test database.

Improve this answer
3
  • This seems cleanest to me, assuming there aren't a lot of touch points - just bypass the sending if you don't want to send. I think @@SERVERNAME or something would be better if this is the same database but restored onto a different server. In my environment I've created a generic mail handler stored procedure so I can change any sending logic in one place - maybe useful to do that even if this is not the approach you choose to solve the immediate problem.
    – Aaron Bertrand
    Commented Jan 16, 2012 at 22:59
  • I like this idea and probably it makes sense to move it to a generic stored procedure so that I can do this in the future. Unfortunately it won't work in this case as a) the 'test' server will become the production server b) there are about 60 calls to sp_send_dbmail.
    – Paul
    Commented Jan 17, 2012 at 4:09
  • Oof. In that case, you could probably just directly alter msdb.dbo.sp_send_dbmail, since it's a regular stored procedure. Insert something like PRINT 'sp_send_dbmail disabled'; RETURN; as the first two statements, then take them back out to re-enable it. Not pretty, but it should work.
    – db2
    Commented Jan 17, 2012 at 13:48
2

Couple of things come to mind.

You could just remove the MAPI profile from the test server. That won't allow xp_sendmail to run - it needs MAPI to work.

Otherwise, you could put a test SMTP server on the network and point your server at that and have it trap the emails. That way you have the emails, but they don't go out to the customers. More work than the other option though.

Improve this answer
0

This worked for me.

USE msdb
GO
EXECUTE dbo.sysmail_stop_sp
GO
Improve this answer
1
  • 1
    Welcome to dba.se - this appears to be a duplicate of the accepted answer already posted to this thread. If you have additional context to add to distinguish your answer, please add it. Otherwise you are encouraged to wait until you have enough rep to upvote the appropriate answer to show that it is the correct one.
    – Peter Vandivier
    Commented Jan 5, 2020 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.