Stored Procedure - CDONTS and HTML Email Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure - CDONTS and HTML Email Problem

Author

Topic

benc007
Starting Member

6 Posts

Posted - 2004-05-22 : 03:43:31

I am new to using sql server stored procedures and CDONTS to send HTML email. I am using "DECLARE @body VARCHAR(8000)", and the stored procedure compiles and sends the email.

When I look at the email in my mailbox, the email gets cut off around 15,000 characters (I have over 20,000 characters in my HTML code).

I tried using "DECLARE @body TEXT" instead of "DECLARE @body VARCHAR(8000)", but I get the error:

"The text, ntext, and image data types are invalid for local variables."

Please help. Any suggestions or information would be appreciated. Thank you in advance.

Ben
benc007@sbcglobal.net

---------- MY CODE ---------------

CREATE PROCEDURE sp_SendNewsletter
AS

DECLARE @result INT
DECLARE @object INT

DECLARE @from VARCHAR(500)
DECLARE @to VARCHAR(500)
DECLARE @bcc VARCHAR(500)
DECLARE @subject VARCHAR(1000)
DECLARE @body VARCHAR(8000)
--The text, ntext, and image data types are invalid for local variables.
--DECLARE @body TEXT

BEGIN
Print 'Sending Test Newletter to 1 Email Address'

--Create the object, if @result <> 0, there is an error.
PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

IF @result <> 0
BEGIN
PRINT 'sp_OACreate Failed'
RETURN @result
END

--HTML email
EXEC @result = sp_OASetProperty @object, 'MailFormat',0
EXEC @result = sp_OASetProperty @object, 'BodyFormat',0

--Send Email using Send method, if @result <> 0, there is an error.
PRINT 'Sending the message using the Send method'

set @from = 'My Newsletter <newsletter@abc.com>'
set @to = 'test@abc.com'
set @subject = 'My Newsletter'

-- ****************************** EMAIL CONTENT *************************************

--the store procedure compiles and works -> Problem is the HTML code
--in @body gets cut off (around 15,000 characters)
set @body = '<HTML><HEAD>Whole bunch of HTML here (over 20,000 characters)</HEAD><BODY>'

-- *************************** END OF EMAIL CONTENT *********************************

EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body, 0
IF @result <> 0
BEGIN
PRINT 'sp_OAMethod Failed'
RETURN @result
END

--Destroy the object, if @result <> 0, there is an error.
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
PRINT 'sp_OADestroy Failed'
RETURN @result
END

Print 'Sending Test Newletter to 1 Email Address - DONE!'

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-05-22 : 05:04:34

If you read all of the article comments, you'll find you just can't do it like that.

If you read the mail queue article there is another way to do it that takes the text handling outside of the proc.

Damian

benc007
Starting Member

6 Posts

Posted - 2004-05-22 : 05:27:02

Damian,

I have read the article comments, and have tried using the following but still can't get it to work:

1) put the HTML into a SQL Server field (TEXT datatype) and dynamically getting the TEXT value into the stored procedure

2) using two VARCHAR(8000) variables in the stored procedure and concatenating them to use sp_OAMethod Send.

I don't know what to try now. Not sure about using the DLL component that was posted by another member. Really stuck...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-05-22 : 05:58:02

It won't work.
You can't pass that much text to sp_OAMethod

Damian

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-05-26 : 18:04:52

I posted in the article's comments where you also raised this question some concerns such as, don't you think 20,000 characters is a lot to send out in a bulk mailing?

Assuming you decide that it's acceptable, then I'd suggest using some other application that can query the SQL Server for the list of email addresses and do the mail-merge outside of SQL Server.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources