Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted
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.

share|improve this answer
    
Perfect. This is exactly what I was looking for, but could'nt seem to find it. Thanks –  Paul Jan 17 '12 at 4:11
1  
@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 Jan 17 '12 at 13:53

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.

share|improve this answer
    
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 Jan 16 '12 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 Jan 17 '12 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 Jan 17 '12 at 13:48

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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