Solved

using xp_sendmail to send blob field content as an attachment

Posted on 2004-04-13
41
1,299 Views
Last Modified: 2007-11-27

Using Delphi 5 I have streamed a PDF document into a blob field  (DT image 16).  I am now trying to email this PDF to a user as part of the development I working on.

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 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.

Any ideas of what I'm doing wrong?

Thanks
0
Comment
Question by:dealclickcouk
  • 18
  • 12
  • 9
41 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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 ?
0
 

Author Comment

by:dealclickcouk
Comment Utility

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
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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).
0
 

Author Comment

by:dealclickcouk
Comment Utility

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

Expert Comment

by:arbert
Comment Utility
How is the PDF extracted?  Did you actually try and open it after the extract?
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
(Also how is the PDF put into a BLOB field ?...)

0
 

Author Comment

by:dealclickcouk
Comment Utility
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
0
 

Author Comment

by:dealclickcouk
Comment Utility

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

Expert Comment

by:danblake
Comment Utility
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)
0
 

Author Comment

by:dealclickcouk
Comment Utility
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'
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
0
 

Author Comment

by:dealclickcouk
Comment Utility

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)
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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...)

0
 

Author Comment

by:dealclickcouk
Comment Utility

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!
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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.



0
 

Author Comment

by:dealclickcouk
Comment Utility

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...
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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..)
0
 

Author Comment

by:dealclickcouk
Comment Utility

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 :-)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:danblake
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"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........
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
0
 

Author Comment

by:dealclickcouk
Comment Utility

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.
0
 

Author Comment

by:dealclickcouk
Comment Utility

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

Expert Comment

by:arbert
Comment Utility
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'
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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....
0
 

Author Comment

by:dealclickcouk
Comment Utility

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...
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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....
0
 

Author Comment

by:dealclickcouk
Comment Utility

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.
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
@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 ?

0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
THE smpt_senmail doesn't send results--that was a future planned enhancement....
0
 

Author Comment

by:dealclickcouk
Comment Utility

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...
0
 

Author Comment

by:dealclickcouk
Comment Utility


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
0
 
LVL 13

Accepted Solution

by:
danblake earned 63 total points
Comment Utility
I was using this to also demo the good documentation on the new mail procedure these guys have put together, which I cannot find on xp_sendmail (such as limitations/new planned releases etc... / per a small single module).

I've also tried to reach the guys on the website to no avail, to try to get some further info, such as a new release date / version for xp_smpt_sendmail

First stop, lets check the newsgroups: I've posted this one @ MS in SQL Server - Tools:  (http://www.msdn.microsoft.com/newsgroups)

Search for: xp_sendmail in the SQL Server -- Tools section
and the query should pop up, I'm hoping one of the MVPs may have seen this one already or somebody knows about it at MS and we can get a quick-turn around.
0
 

Author Comment

by:dealclickcouk
Comment Utility

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

Assisted Solution

by:arbert
arbert earned 62 total points
Comment Utility
"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..."


Like I said above, you can build one SP or XP if you prefer and also call textcopy to extract the blob and then xp_sendmail can easily send it....
0
 

Author Comment

by:dealclickcouk
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Post how you called it.  I set it up here without problems.
0
 

Author Comment

by:dealclickcouk
Comment Utility

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now