Sending an email using T-SQL

Question

Post reply

Sending an email using T-SQL

MaJ

SSC Veteran

Points: 292
More actions
February 26, 2006 at 9:30 pm

#103099

What I require is the results of a stored procedure or a query to be emailed out.
Can anyone suggest the easiest method to set up?
Thanks

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

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Sergiy

SSC Guru

Points: 110209

February 26, 2006 at 9:37 pm

#623391

xp_sendmail

_____________
Code for TallyGenerator

MaJ SSC Veteran Points: 292 More actions · Answer 2

do I need SQL-Mail for that? because I don't seem to have that it listed anywhere in my Enterprise Manager?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Yes, xp_sendmail uses MAPI and need lots of icky configs to work and have all sorts of issues.

A 'better' way to mail-enable SQL Server is to use xp_smtpmail instead http://www.sqldev.net/xp/xpsmtp.htm

It's not MAPI, instead uses SMTP, takes about 15 mins to get working and (for me anyway) doesn't give you any grief whatsoever.

I recommend it.

/Kenneth

MaJ SSC Veteran Points: 292 More actions · Answer 4

The current version does not provide @query support like the one found in SQL Mail

I need to be able to query the database and email the results (using a trigger or and sp). xp_smtpmail does'nt seem to allow this or am I interpreting this wrong?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Ah, you're correct. It doesn't support a @query parameter like xp_sendmail does. However.....

if the result of the query is placed in a file on disk, you could then attach it with xp_smtpmail.

This may seem like a kludge, but given the inherent bad rep of SQL Mail, I'd still favor this method and sacrificing the @query parameter in favor of - stability, ease of use, no fuzz that xp_smtpmail provides.

Been there as well, xp_sendmail, SQL Mail, MAPI, Exchange servers, Outlook clients et all was a true PITA to a) get working at all, and b) to stay working once a) was solved.

Once I moved to xp_smtpmail it was a question of downloading it, and 15 mins later it was working, and has ever since. I don't regret the move for one second

(though I don't have a @query parameter, but there are ways around it like attach)

/Kenneth

Vladan SSC-Insane Points: 22072 More actions · Answer 6

We started with xp_sendmail, but later on realized, that it has certain limitations and rewrote most procedures that mail results to use smtpmail.

The only problem we needed to solve was that users complained that it is stupid to have to open attachments with a small table - they prefer to see the results at once in the message. However, the size of the table is unpredictable and the same procedure can sometimes generate 5 rows and sometimes over 1000. Once you exceed certain limit, you can't incorporate the file into message body and have to attach it.

What we did is that: Results of the procedure are saved as HTML file using sp_makewebtask. Then we call another procedure that checks the size of that file - files up to about 64kb can be included in the body of message, larger have to be attached - and calls xp_smtp_sendmail with corresponding parameters which either attach or incorporate the HTML file.

HTH, Vladan

Bill Fagg Grasshopper Points: 10 More actions · Answer 7

Hi, if it's a regular requirement, why don't you use reporting services?

MaJ SSC Veteran Points: 292 More actions · Answer 8

This seems like an excellent work around - many thanks.

MaJ SSC Veteran Points: 292 More actions · Answer 9

Using xp_smtp_sendmailI've tried the following with the correct server setting but it returns a value of 1 (error). I think the smtp server requires authentication, any way around this?

declare @rc int

exec @rc = master.dbo.xp_smtp_sendmail

@FROM = N'MyEmail@MyDomain.com',

@FROM_NAME = N'Joe Mailman',

@TO = N'MyFriend@HisDomain.com',

@cc = N'MyOtherFriend@HisDomain.com',

@BCC = N'MyEmail@MyDomain.com',

@priority = N'HIGH',

@subject = N'Hello SQL Server SMTP Mail',

@message = N'Goodbye MAPI, goodbye Outlook',

@type = N'text/plain',

@attachments= N'c:\attachment1.txt;c:\attachment2.txt',

@server = N'mail.mydomain.com'

select RC = @rc

go

Vladan SSC-Insane Points: 22072 More actions · Answer 10

Hello,

I'm not sure whether this is the reason, but... parameter @server in your code contains full address, used when communicating with the server from outside of domain. I suppose you're in the same domain with the mail server - so try to replace it with just the name of the server (in your example "mail"). That's how we specify the server and it works fine; when I tried to replace it with full name including domain, I got "error connecting to server". Maybe that's it?

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 11

Could be a DNS resolution problem for you Vladan.

I use a fully qualified FQDN 'server.abc.def.se', and it works with no problems.

Though I must confess I don't quite know what the smtp guys did to set this up - I just asked them to make make server to be able to talk to the smtpserver and be able to send mails.

It could be possible that I told them servername and the account for the SQL Server, don't really remember.

What I do know is that there was something they had to do 'over there' at the smtp end.

/Kenneth

MaJ SSC Veteran Points: 292 More actions · Answer 12

The server name I entered is correct (mailrouter.man.ac.uk) as it returns a value zero when using the ping command. The server requires authetication for me to send email from it. How can I achieve this?

Thanks.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 13

Talk to the mailserver guys and ask them to make what need be so your server will be able to send mails through that server. (I assume it's like setting up any other 'normal' user)

/Kenneth