October 30, 2002 at 8:24 am
Good Morning
I have been so use to saying C-Dont's for so long that I had to think a bit on the actual spelling of it 🙂 so if I got it wrong, please don't smirk too much..
I have a question about CDONTS as compaired to xp_sendmail.
In xp_sendmail I can use @Query to create my message body from a select statement. But I can't seem to locate any documentation on how to do the same for CDONTS.
My problem is that my body is greater than 8000 characters and TEXT is an invalid type for a local var. And I am creating HTML output 🙂
So I created a DLL which I use in my stored procedure to deal with the problem of not being able to figure out how to use a query with CDONTS and it deals with sending the mail but it is an evil work around.
Does anyone out there know if and how to query a text column into the body of the CDONTS email?
Thank you.
David.
--> David V.F. Burton <--
--> David V.F. Burton <--
http://www.siantrex.net
November 1, 2002 at 8:16 am
Here is a solution to get XP_SENDMAIL to send a mail message longer than 8000 characters. It uses a global temp table to hold the body of the email. Will this work in your situation?
Send messages longer than 7,990 characters:
This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @query parameter.
CREATE TABLE ##texttab (c1 text)
INSERT ##texttab values ('Put your long message here.')
DECLARE @cmd varchar(56)
SET @cmd = 'SELECT c1 FROM ##texttab'
EXEC master.dbo.xp_sendmail 'robertk',
@query = @cmd, @no_header= 'TRUE'
DROP TABLE ##texttab
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 2, 2002 at 6:29 am
Thank you for your reply.
But unfortunattly, the problem is not using xp_sendmail
it is that I am attempting to send HTML formatted Email and xp_sendmail will not do that for me.
I used to use xp_sendmail when all I needed was text in my hudge giant report, but upper management got all HTML'ish with their reports and made me do the same :).
My kingdom for a server 2000 🙂
My email that I send is now 400,000 plus characters (now that is is HTML) and I build it in a text field by creating a pointer to that text field and appending new text to that field and then selecting it back out into the email.
since xp_sendmail will not allow me to send HTML formatted email (well at least as far as I have ever been able to figure out) I moved over to CDONTS to send my email, but...
I can not figure our how or if I can select into the body of CDONTS.
But the cool thing is that my work around that I mentioned in my first post may have lead me down the path of EVIL!!! I created an Extended Strored Procedure which does everything that xp_sendmail does but has the added value of sending HTML formatted emails.. Unfortunattly is is still just a workaround until I totally run out of options with this CDO thing.
I call my new xp (xp_sendhtmlmail) and if there is no way to select from a text field into the body of CDONTS (or CDOsys but we are still on NT) then I will release all my code for xp_sendhtmlmail to this site in the form of an article..
I am actually having a lot of fun with this solution, but I am still looking for the answer to my question on selecting into CDONTS..
Well I have rambeled on enough.. 🙂
Dave..
--> David V.F. Burton <--
--> David V.F. Burton <--
http://www.siantrex.net
November 4, 2002 at 7:23 am
XP_Sendmail is fully capable of sending HTML formatted emails. It doesn't format them for you, but it also doesn't care what is in the email. Format it yourself, and pass the html source text to the send. We have been sending embedded hyperlinks and pictures in our mails for quite some time.
The only issue we have ever had with it is situations where the end user's mail program isn't able to recieve HTML formatted messages, and they show up as html source code.
November 5, 2002 at 2:47 am
Wow.. Really.
I have never been able to get xp_Sendmail to actually send HTML formatted text as an HTML Email.
Maybe Outlook (Exchange Server driven) doesn't like receiving html text, but I have formatted my emails with fully qualified HTML pages and all I get on the Outlook side is the text of my HTML document like this: <HTML><HEAD>...</HEAD><BODY>...</Body></HTML>
But.. That is ok.
I am going to close this thread now as my work around seems to be way cool and is nolonger evil.
I rewrote it over the weekend to be more memory friendly and it sits on the server as a happy little extended stored procedure using CDONTS and doing everything I want it to do..
I figure, if you can't beat them... Rewrite them 🙂
Thank You: Greg Larsen and Scorpion_66 for your help here.
And I will look in to sending HTML formatted emails through xp_sendmail to a non exchange server just to see if it was our exchange server all along.
Dave.
--> David V.F. Burton <--
--> David V.F. Burton <--
http://www.siantrex.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply