Can anything except [sp_send_dbmail] send emails out?

Question

Post reply

Can anything except [sp_send_dbmail] send emails out?

RossRoss

SSCommitted

Points: 1672
More actions
March 24, 2014 at 4:32 am

#305351

I'd like to enable emails on a staging server to properly test it before going live, but obviously don't want emails being sent to real clients.
I have edited [sp_send_dbmail] to replace @recipients with my own email address, and clear out the @copy_recipients and @blind_copy_recipients parameters after they have been passed in, so I can see everything that would ordinarily be sent to clients if this was a production server, and nothing actually gets sent anywhere except my own address.
I know xp_sendmail no longer exists as of SQL Server 2012, but are there any other SPs or XPs that can send emails that I need to consider before enabling email on the staging server?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

thava SSCrazy Points: 2275 More actions · Answer 1

i think this link will help you

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

RossRoss SSCommitted Points: 1672 More actions · Answer 2

Thanks thava, I'd seen that page and it only mentions sp_send_dbmail, so I'm just wondering if there are any other procedures that can send email too? Anything not mentioned in that article?

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Yes, you can use your e-mail address to test send the messages. There should not be any issue.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

RossRoss SSCommitted Points: 1672 More actions · Answer 4

Yes, I'm using my email address, I'm asking if there are any other stored procedures that need to be changed, so that live clients do not get staging emails sent to them?

thava SSCrazy Points: 2275 More actions · Answer 5

If you closely watch the link the store procedure is in msdb did you update it in msdb Database, please don't do that , create a wrapper proc for sp_send_dbmail and use that proc in your testing environment hope you got it, i think

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Lowell SSC Guru Points: 323518 More actions · Answer 6

i've done a CLR which sends email, but that assumes you have the ability to install CLR assembleis on your machine; .NET has the built in classes to do that, but you need a CLR to access them.

I even have an example that reads an email box, which is something you can't do with sp_sned_dbmail..it's outbound only, and i wnated a proof of concept on reading the mailbox back for responses.

it's also possible to send email from the command line/xp_cmdshell calling some old sp_OaCreate procedures to call third party DLL's.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RossRoss SSCommitted Points: 1672 More actions · Answer 7

thava (3/24/2014)
If you closely watch the link the store procedure is in msdb did you update it in msdb Database, please don't do that , create a wrapper proc for sp_send_dbmail and use that proc in your testing environment hope you got it, i think

Thanks, but that's not what I'm asking, I need to change it in msdb because there are sprocs calling it directly, and wrappers in several DBs all calling it, this is only a staging server, so I am not worried about changing it in msdb.

My question is 'does anyone know of anything except [sp_send_dbmail] which can send emails?'

RossRoss SSCommitted Points: 1672 More actions · Answer 8

Lowell (3/24/2014)
i've done a CLR which sends email, but that assumes you have the ability to install CLR assembleis on your machine; .NET has the built in classes to do that, but you need a CLR to access them.
I even have an example that reads an email box, which is something you can't do with sp_sned_dbmail..it's outbound only, and i wnated a proof of concept on reading the mailbox back for responses.
it's also possible to send email from the command line/xp_cmdshell calling some old sp_OaCreate procedures to call third party DLL's.

Thanks Lowell, I'm familiar with the very few CLRs we have in place and none send emails, xp_cmdshell however, is something I hadn't considered. It may be calling things externally to send emails, so I'll double check this, thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 9

Ross i misread your requirement, and picked up where you said "is there any otherway to send email."

AFAIK you cannot change the receipient of an email that was sent to sp_send_dbmail. it's stuck in the service broker asychronously, and processed, and i don't think there is any way to alter the broker to modify it.

the right thing to do on the dev server i think is to simply break the mail so it doesn't send out at all, but throws all mail in the fail mail instead. changing the smtp server to fake.fake.com or something, and then if you need to you can just look a the mails;

SELECT

sent_status As Reason,

err.[description],

mail.*

FROM [msdb].[dbo].[sysmail_allitems] mail

inner join [msdb].[dbo].[sysmail_event_log] err

ON err.mailitem_id = mail.mailitem_id

order by mailitem_id desc

so I'm saying to modify any [server name] on all the accounts on the dev server so the mail server is invalid.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RossRoss SSCommitted Points: 1672 More actions · Answer 10

Lowell (3/24/2014)
...you cannot change the receipient of an email that was sent to sp_send_dbmail. it's stuck in the service broker asychronously, and processed, and i don't think there is any way to alter the broker to modify it.

I've edited sp_send_dbmail so that as soon as the SP hits the BEGIN statement, the following code is executed:

SET@recipients = 'MyEmail@email.com'

SET@copy_recipients = NULL

SET@blind_copy_recipients = NULL

This should change the To, CC, and BCC parameters so that it only sends to my own email address, will this not work?

Lowell SSC Guru Points: 323518 More actions · Answer 11

Ross.M (3/24/2014)
Lowell (3/24/2014)
...you cannot change the receipient of an email that was sent to sp_send_dbmail. it's stuck in the service broker asychronously, and processed, and i don't think there is any way to alter the broker to modify it.
I've edited sp_send_dbmail so that as soon as the SP hits the BEGIN statement, the following code is executed:
SET@recipients = 'MyEmail@email.com'
SET@copy_recipients = NULL
SET@blind_copy_recipients = NULL
This should change the To, CC, and BCC parameters so that it only sends to my own email address, will this not work?

i hang my head in shame; i did not even consider modifying the procedure itself.

you are absolutely correct, that would intercept everything, and still let you confirm emails are working and sent out.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RossRoss SSCommitted Points: 1672 More actions · Answer 12

lol no problem 🙂

Don't suppose you know of any other SPs that can send emails without using sp_send_dbmail?

I'm currently checking through xp_cmdshell calls like you suggested earlier, and using Redgate SQL Search to identify anything with the word 'mail' but it's slooooooowwww going with so many SPs :\

Lowell SSC Guru Points: 323518 More actions · Answer 13

i am sure that everything revolves around sp_send-dbmail; nothing else does the same work via a different name, but certainly jobs and procs could exist, but they all call that central stored proc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RossRoss SSCommitted Points: 1672 More actions · Answer 14

Lowell (3/24/2014)
i am sure that everything revolves around sp_send-dbmail; nothing else does the same work via a different name, but certainly jobs and procs could exist, but they all call that central stored proc.

Excellent, thank you, should be safe to proceed with just that sproc after it's been edited then 🙂

For the record, the reason I'm not going with your suggestion of breaking it and reading everything from the failed log, is that it's not just me testing things, there are other developers building apps, and some managers etc. who all want to test things, they wont all be able to access the failed email log from SSMS, but by redirecting everything to a group email address, everyone involved can see what's being emailed out.