SQL2000 xp_sendmail Large Messages

Question

SQL2000 xp_sendmail Large Messages

Robin Pearce

SSC Veteran

Points: 205
More actions
August 31, 2007 at 5:20 am

#125447

I am building a TSQL Job procedure to send large messages with SQL2000 xp_sendmail. The output text size is about 8000-9000 chars. I am having problems making either of the two BooksOnline examples work, because I can't see how to use a text type variable:
(1)BooksOnline gives an example write to an attached file using @attach_results and @query. Problem is my @query is more than a simple SELECT, I want to reformat the results using CAST, spaces,etc to produce nice neat columns. How do I get this to work? I can't store the concatination result in a text as you can't use text type for a local variable.
(2)BOL gives another example purportedly avoiding VARCHAR limitations by writing to a temporary table. It contains the line
INSERT ##texttab values ('Put your long message here.')
...but how do I get my concatinated, reformatted text results in place of 'Put your long message here.' ?
I don't care which method I use, I would appreciate any help with the precise syntax to achieve this. Is there a third method?
Many thanks, Rob Pearce

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

Hello,

this probably isn't the best way to format output... maybe you should consider using sp_makewebtask, which will allow you to create a HTML file with table, and then attach the file to the message?

Our solution for mailed reports is sp_makewebtask for formatting, and xp_smtp_sendmail for sending the results.

Also, with EXEC master..xp_getfiledetails @MessageFile you can find out how large the file is; files under 64k can be incorporated directly into message body, larger ones can only be sent as attachment.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Vladan, Be careful using xp_getfiledetails, it goes away when you move to SQL Server 2005.

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

Thanks Lynn, good point... we already have one server running SQL2005 and we had to modify the procedure for sending emails. We also use sp_send_dbmail in 2005 instead of SMTP mail.

I would recommend to anyone, if you are sending e-mail reports, write your own "bridge" procedure - e.g. send_mail - which will accept parameters, do all necessary things and invoke the actual procedure that sends the mail. This way, if you decide to (or have to) change something in the way you are sending mails, you can just modify this one procedure - in our case, after changing to 2005 we deleted the part with getfiledetails and redirected the call from xp_smtp_sendmail to sp_send_dbmail - and all the other procedures can stay as they were before.