using xp_sendmail to send blob field content as an attachment

The above does send an email, however when recieved the attachment is not recognised as a PDF, ie when loaded into Adobe it thinks its corrupt.
Try this:
Sending the file across a a .ZIP (sometimes, a file is corrupted when sent across the internet and does not arrive in the form sent).

Or is this only to the local network ?

It is not local, so maybe this could be it although I'm not so sure as sending manually (not from a blob) doesn't cause the problem, but could you provide the code on how to zip the file with the above method, and if it works will award u the points.

However if anyone had a non-zip method out there they can also have the point.

Thanks

This has previously been asked here before:
https://www.experts-exchange.com/questions/20930673/DTS-Passing-Global-Variable-into-Execute-Process-Task-Parameters-field.html

And input of the file to be extracted: http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=106100

Its not too difficult to generate a xp_cmdshell implementation for unzipping (requires winzip + sa permissions).

No, this isn't the problem, it must be to do with the way the pdf is extracted from the blob field

How is the PDF extracted?  Did you actually try and open it after the extract?

(Also how is the PDF put into a BLOB field ?...)

the PDF is on a blob field....

EXEC master..xp_sendmail @recipients = 'me'
        @subject = 'Test attachment email',
        @message = 'This is the stuff you requested.',
        @attachments = 'test.PDF',
        @attach_results = 'TRUE',
        @no_header = 'TRUE',
        @query = 'select doc from PDFTBL WHERE ID=5'

the stmt you see selects the blob field, takes away the headers, and then sends the content in file test.pdf, I though this would do the trick

The PDF is streamed into the BLOB field within Delphi 5 code, aftering being created by a 3rd party control (wPDF).

Can you check the data can still be read by Adobe after being placed into the server, with a simple extract:

bcp "SELECT doc from PDFTBL WHERE ID=5'" queryout TEST1.pdf -Sservername -Usa -Ppassword
(or remove -U / -P and use: -T trusted connection).

And then verify the output of the file can still be read by Adobe.

(It may prompt for a format file to be created)

sorry to be dumb, but where do I run this command, tried it in query analyzer but it gives me an error...

incorrect syntax near 'queryout'

Its a CMD shell utility.  (You run it from the command prompt, start run : cmd or command -- depending on your OS)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

I have tried the BCP example and yes it does output the pdf fine and is read OK by Adobe.

I've noticed in the recieved email that the attchment size is only 32kb when the actual pdf is 195kb (ie the size of the output from the BCP command)

Good, looks like we are now getting somewhere...
So it is definitly storing the results correctly within the database -- good.  (Some tests are just to eliminate aspects, looks like your delphi input is working and the xp_sendmail is bugged...)

This looks like the maximum output attachment size for xp_Sendmail is a bugged..:  (Have you had a look at these ?)
http://support.microsoft.com/default.aspx?scid=kb;en-us;307948&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;317797&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;EN-US;280720  (Xp_send_mail -- output format in Unicode within SQL Server 2000 / ANSI in SQL 7 difference in versions...warning generated when file input / output from the system.)
http://support.microsoft.com/default.aspx?scid=kb;en-us;314304&Product=sql2k (ANSI bugs if requiring to send file out in this format)


I would now like some further information : SQL Server Version / Edition to facilitate some further debugging.

What tool are you using to run the output xp_sendmail command ? (DTS/osql/QA...)

I have SQL Server 2000 enterprise edition, at present I am running the xp_sendmail from SQL query analyzer, but this is just while I'm testing, eventually I want to call it from Delphi.

I have read the post regarding ANSI etc and am downloading the latest patch now (3a) I can't remember what I'm currently up to (or how to check!) so may be this will take us closer!

you can run: select @@version to get a short form of this, or run: exec master..xp_msver for more detailed information.

Can you also try to output using osql (its another command line utility -- I want to check its not a problem with SQL QA and the maximum results/column// size of output buffer limitation or something similar to that )

osql -S 127.0.0.1 -d Trigger_Generator -E -n -r -I -Q"<your_query>" -o output2.txt
<O = Our SQL = SQL query : -S <server_name/ip/instance/>  -d < database_name> -E <send errors to output2.txt -Q<place your query here, exec xp_send mail...> ...>
And check the output file (output2.txt), which should contain any errors and your output file (.PDF) should be generated from within this context.

OK, tried that, mail sent still had 32 kb attachment and the only content of output2.txt was "Mail sent."

BTW thanks for all your pateint help...

I think dealclickcouk you are the patient one...for me its a no worries problem, business as usual.  I'm just wondering where arbert has run off to...

I'm used to having somebody screaming in my ear going why does it not **** work, its **** this doesn't work, or why havn't you managed to get **** done yet (when you're propping up a server balancing it in a server room  in the middle of a hardware upgrade -- working on a system that has just crashed when a client is screaming in your ear -- why's this site gone down, my 300+ users can't access the system -- get it sorted (when the links to all the external RAID cabinets have been lost)), and its a waste of time all that data-modelling stuff you do, ...you're too technical theres' no room for technical people in IT (That was the best one I've heard so far !- from an IT software company !) .....  I am glad you are having a good day and are very relaxed about the problem that you are facing.

Thats is one cool relaxed cookie at the other end - I've got this tracked, if we determine this is a bug/can reproduce this on another system we've got it tracked down, it may be it's some really isolated problem such as it only occurs when using a text field or something like this (hence why nobody else has seen this problem -- yet or its a service pack issue / update required and we need to verify with MS the problem..)

The service pack it taking forever to download, I have a 1.5mb  connection, but still only at 16% , 6 hrs to go!

Still at least its a nice day, sunny here it Sweden :-)

You would like it here too at the moment, clear blue skys , sunshine its' lovely.
(Went out on my bike for a bit of a cross-country cycling in the woods earlier).

I'll come back when you next re-post and we'll resume this one then.

"I'm just wondering where arbert has run off to..."

You know what--some of us have paying jobs and can't sit on here all day.....Besides, there weren't any updates on this question till today anyway........

Ok, you know that the blob is stored correctly.  In earlier releases of SQL Server there was a unicode problem.  Just for giggles, there is the @ansi_attachment parm on the Xp_sendmail proc.  Will you try the same procedure you did above setting this parm to true and then false.

EXEC master..xp_sendmail @recipients = 'me'
        @subject = 'Test attachment email',
        @message = 'This is the stuff you requested.',
        @attachments = 'test.PDF',
        @attach_results = 'TRUE',
        @no_header = 'TRUE',
        @query = 'select doc from PDFTBL WHERE ID=5',
        @ansi_attachment=true

OK, tried with both options, with ansi set to true , produces the same results as with no param set, with set to false the attachment size goes from 32kb to 16kb,  but still unreadable by adobe.

OK, applied SP3a and tried again what arbert suggested, however same results as above... :-(

I just setup a test table and tried this--I only get 49k back from a 1.5meg file as well--looks like the results can't email a text/image datatype.

This is a hodge, but in your proc, you could program a textcopy output to the original file and then email that just using @attachments instead of @attach_results.




xp_cmdshell 'textcopy /tPDFTABLE /cDOC /uUSERNAME /pPASSWORD /fc:\output.pdf /o /sSERVERNAMEHERE /dDATABASENAMEHERE /W"where ident=5"'

go

EXEC master..xp_sendmail @recipients = 'me'
        @subject = 'Test attachment email',
        @message = 'This is the stuff you requested.',
        @attachments = 'c:\output.pdf',
        @no_header = 'TRUE'

of course, you would have master..xp_cmdshell in the above example and you would also need to put the full path in to where your textcopy.exe is installed (usually in the microsoft sql server binn directory).

I kind of see why you can't email blob results directly--there isn't a header or mime type or anything.  How does email know what you're sending?  It's just binary data....

I understand your comment, but outputing the contents of the field to a phsical file and calling it pdf is enough, so why should the method I'm trying not be OK, after all the result header is turned off so only field content, and the name of the file that the content goes into tells the receiving computer what file type is has...

It would be enough if that's how it works.  When you use @attach_results, it doesn't work in conjuntion with @attachments.  When you use @attach_results, in the background SQL creates temp files on the server and then uses the temp files in the email--it doesn't know to relate the results with the attached file you specified....

Maybe I'm missing something, but I thought, as per SQL Server doc's, that when you attach results and provide an attachments param the results and stored in that file, therefore the results are related.

If I'm missing the point please let me know.

BTW: I tried your solution above, but when I run it, it complains because I have not set the @query parameter and does not send anything.

@arbert / @dealclickcouk (@dealclickcouk -- I think you're right)
I thought the attach_results worked in conjuncton with attachments -- otherwise how do you know what to call the output file on a destination server ? (From BOL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp)
If attachments is not NULL and attach_results is true, the first file name in attachments is used as the file name for the results. If attachments is NULL, a file name is generated with a .txt extension. The default is FALSE, which means that the result set is appended to the message.
--> This is the attachments parameter and the attach_results parameter in xp_sendmail.

So specifiying a file name test.pdf is OK.

Looks like my earlier guess was pretty good then guys:it may be it's some really isolated problem such as it only occurs when using a text field or something like this....
I think the hint is given here:
http://www.winnetmag.com/Articles/Print.cfm?ArticleID=3097

There is an alternative called xp_smpt_sendmail, I think xp_sendmail may be bugged.
Have a look at sqldev.net homepage -- they are looking to release a version where the mail length attachment size can be > 64KB, but I'm not holding my breath.....

--> I know we can just attach the BCP output file with a query, as an alternative (even zip it across, this way it does not have to generate the output -- anybody fancy testing this.., its just attaching a pre-existing file ?...), prehaps we should raise a PSC/Email MS on this one.


Anybody know of anybody who has managed to produce a long attachment , or even what the MS testing limits are on xp_sendmail ?

THE smpt_senmail doesn't send results--that was a future planned enhancement....

My SQL Svr installation does not seem to have xp_smpt_sendmail (or xp_smtp_sendmail, if the other was a typo), is this an additional xp that does not come as standard?  If so where can I get it?

I guess the question now is, whether this is a feature or a bug of SQL Svr, and if the later how so I report this to MS?

Unless someone has some as yet undisclosed clever way round this.

Thinking about it, I guess I could write a xp in delphi to write the file to temp dir and then use std xp_sendmail within the dll/xp, then at least going forward there is one xp to call, but this is still creating a temp file, which I wanted to avoid as 100s of users will be using the system and really don't like the idea of tmp docs getting mixed up.  The PDF will contain private financial info...

Thanks again for your help getting this far...

http://www.sqldev.net/xp/xpsmtp.htm#Known_limitations

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

It seems thats xp_smtp_sendmail doesn't fit the job eithe, unless a newer version is available, this documentation is for ver 1.1.0.8

Cool, again appreciate your time & effort on this, wouldn't have got this far without it...

Arbert  I tried your solution above, but when I run it, it complains because I have not set the @query parameter and does not send anything.

Post how you called it.  I set it up here without problems.

sorry, my fault, I left the @no_header param in, and obviously shouldn't be there!