Using xp_sendmail to send Word Documents stored as image
-
Hello !
(First post so please don't flame me
)I've got some MS Word documents stored in the database and I'd like to use SQL Mail via xp_sendmail to e-mail them to some users.
I don't want to persist the documents to a file location before sending, I'd like to use the @query parameter to retrieve them straight from the table to create the attachment.
The SQL I've got at the moment is:
EXEC master.dbo.xp_sendmail
@attachments = 'test.doc',
@recipients = 'someone@somewhere.com',
@query = 'SELECT TOP 1 doc FROM MyDB.dbo.MyTable',
@subject = 'Test',
@message = 'Test',
@attach_results = 'true',
@no_header = 'true'
Sure enough the e-mail is sent including the attachment but the document is 'corrupted' (for want of a better word), - when I open it in Word all I see is a garbled mess.
Is this going to be possible to fix or am I wasting my time ?
Any thoughts would be much appreciated
Thanks,
Rich
-
Hi
The garbled mess is Unicode output from xp_sendmail. To change output coding to Ansi try to set xp_sendmail parameter
@ansi_attachment = 'true'
This link from MS KB should be useful
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280720
Max
-
Thanks Max, but tried the ANSI output yesterday, it gives me a garbled Hexadecimal mess !
-
You are going to have to save the word document to a file then attach the file to the email, and then delete the file after the email is sent, the query outputs the word document as the body of the message and no email client that I know of supports an word document as the body of the message.
Andy
-
Nope.
The @attach_results = 'true' ensures that the query result is sent as an attachment rather than in the body.
-
You may disagree with David's wording, but he's right. You need to extract the Word doc from the database, save it to a directory, attach that file to the mail message, send the mail message, then delete the file. Trust me, I do this all the time. In fact, we don't store Docs, GIFs, JPGs, etc in databases if we can avoid it simply to avoid this overhead. We keep them in a directory and only store the name of the file in the database. That way we can just form a path+filename and attach it (or display it in a web page).
If you have the opportunity you might want to rethink storing the docs in the DB vs. keeping them separate and only keeping track of the name.
-
(It's not the wording that I disagree with. I think he got the wrong end of the stick, - read the last past of his post i.e. "the query outputs the word document as the body of the message" - that is incorrect.)
Unfortunately I'm stuck with a legacy system that's been around for years so I'm going to have to keep going with the old method of storing the documents in the DB whether I like it or not.
I know it's possible to mail the document several other ways but this way would be a neater solution, unfortunately it looks like it's not going to be possible if this is anything to go by :
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20952096.html
-
It looks like they never really resolved the xp_Sendmail issue in that thread...
I never use xp_Sendmail -- too many issues with it. We use the CDO.Message in an ActiveX Task in DTS. First step extracts the relevant blob to a file and sets a global variable to the file name, second step uses CDO.Message to send an SMTP message with the file attached.
If you're doing Java or ASP or .Net coding you can do it all in one routine.
-
"It looks like they never really resolved the xp_Sendmail issue in that thread..." - exactly
I think I'm going to forget it and just send the recipient a link to an ASP page where they can download the document, oh well.....
-
I think I'm going to forget it and just send the recipient a link to an ASP page where they can download the document, oh well..... So you're going to extract the blob to a file somewhere, construct a URL and put it in the body of the email? Since you already have the file in a known directory (otherwise how could you construct the URL?) why not attach it to the email?
You may be overthinking this. It's really fairly straightforward.
-
The present mechanism is :
1. Version zero of a document is automatically generated and e-mailed to a user using CDO.
2. The user edits the document and stores in the database via an 'upload' ASP page on the intranet.
3. All versions of the document are available via a 'download' ASP page on the intranet.
Thus (to make life simpler) I'm simply going to store version zero (can't understand why this isn't done already!) in the database and send the user a link to the 'download' page.
This means that all documents are stored/retrieved in a uniform fashion, - it may not be considered elegant but I am not re-engineering the database/intranet - it's soooo wrong that it would take too long. (it was all done in the 5 or 6 years prior to me joining the company)
"So you're going to extract the blob to a file somewhere, construct a URL and put it in the body of the email? Since you already have the file in a known directory (otherwise how could you construct the URL?) why not attach it to the email?"
Nope, there's no need to persist the document to a file, - the documents are retrieved via an ASP page on the intranet using ADO and Response.BinaryWrite (chunking if necessary) to stream it to the client. The hyperlink in the e-mail will contain a key value in the querystring that will be used to retrieve the appropriate document.
"It's really fairly straightforward"
Yes, I know
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply