CdoNtsMail - What are the actual size limits to the "Body"

Question

Post reply

CdoNtsMail - What are the actual size limits to the "Body"

Jeff Moden

SSC Guru

Points: 1004365
More actions
August 26, 2008 at 7:08 am

#191657

Howdy folks... working with something a bit new to me.... CdoNts...
We have the following stored procedure setup (I didn't write it or there would certainly be some different sized variables)...
CREATE PROCEDURE sp_send_cdontsmail_attach
@From varchar(100),
@To varchar(1000),
@Subject varchar(100),
@Body varchar(8000),
@Attachments varchar(1000) = null,
@cc varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @cc
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OASetProperty @MailID, 'BodyFormat', '0'
EXEC @hr = sp_OASetProperty @MailID, 'MailFormat', '0'
EXEC @hr = sp_OAMethod @MailID, 'AttachFile', NULL, @Attachments
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
8k bytes seems very restrictive as do the variable sizes for BCC and CC, etc, so I have a couple of questions...
1. Things like the EXEC command allow the concatenation of variables to overcome the 8k barrier... something like this...
[font="Courier New"]EXEC (@variable1+@variable2+@variable3+...)[/font]
Can CdoNtsMail do the same with the "Body" portion of the stored procedure? In other words, would something like the following work?
[font="Courier New"]EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body1+@Body2+@Body3[/font]
If not, got a clever trick to accomplish the same thing?
2. Do you have a good URL link that explains all of the variables available to CdoNtsMail and their limitations/work arounds?
3. Any sage advice on usage?
I'm still Googling for information but any help on this sure would be appreciated. Thanks folks. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

ps... I've found the following URL and I'm in the process of reading all the links contained in it...

http://support.microsoft.com/kb/312839

Even if you don't have a better place to look, I sure wouldn't mind hearing from folks with some experience with CdoNtsMail... thanks again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Update: I've found that the underlying code in the DLL uses the STRING data type for the things that take string data... in C#, that holds 2 billion bytes (2x1024³)... guess all I have to do now is some simple tests. Since this is for SQL Server 2000, if concatenation of multiple VARCHAR(8000) variables doesn't work, I'll try throwing the SQL Server TEXT data type at it and see what happens.

Heh... this is turning out to be more of a discovery blog thread than a request of help. I don't mind, though... it's my nature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Update:

What a huge PITA SQL Server 2000's TEXT data type is...

You can create a sproc with a TEXT datatype variable as one of the parameters. Trouble is, you can't get a pointer for it AND you can't make a direct assignment to it.

Why bother with text? Because you also can't do a calculation in a parameter of an EXEC call, so no concatenation of multiple VARCHAR(8000) or NVARCHAR(4000) variables to extend the BODY past the limits of a single variable. If anyone has a trick to do that with CDONTS, I'd sure appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 4

Hmmm...

I do not remember where I read that, but apparently CDOSYS is much superior to CDONTS.

And I am getting confused here, is CdoNtsMail different from CDONTS ? A Web search often shows both used for the same thing... such as

a simple asp page using cdonts mail component to send out email ...

Any advantahge of using CDONTS over using CDOSYS ?

(I have posted some some using CDOSYS at http://www.sqlservercentral.com/Forums/Topic527864-110-1.aspx)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

J (8/28/2008)
Hmmm...
I do not remember where I read that, but apparently CDOSYS is much superior to CDONTS.
And I am getting confused here, is CdoNtsMail different from CDONTS ? A Web search often shows both used for the same thing... such as
a simple asp page using cdonts mail component to send out email ...
Any advantahge of using CDONTS over using CDOSYS ?
(I have posted some some using CDOSYS at http://www.sqlservercentral.com/Forums/Topic527864-110-1.aspx)

Dunno about the advantages of either because this is really my first exposure to either. And the "CdoNtsMail" and "CDONTS" references are meant to be the same thing. Sorry for the confusion.

7. THE MESSAGE
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @ls_Fileslist
-- FOR HTML USE 'HTMLBody' NOT 'TextBody'

COOL! You mean to tell me that the 'TextBody' and 'HTMLBody' parameters will take a list of filenames to use the content of as the body of a email?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 6

Text body and HTML body will take whatever you put in the local string variable. A list of file names (separated by CHAR(13) should indeed do what you want.

And the stored procedure I referred to in my previous post does not require that an SQL Mail Account be setup or that Outlook be installed and running on the SQL Server host machine. Your purpose is to SEND e-mails, not receive them, right ?

Regards

J-440512 SSCertifiable Points: 6285 More actions · Answer 7

Forgot to tell you.

If it appears the stored procedure executes but that the actual e-mails appear not to be sent, SMTP handles throws them in the c:\Inetpub\mailroot\badmail directory.

J-440512 SSCertifiable Points: 6285 More actions · Answer 8

Also,

Rechecked my code, yes sending a list of file names is exactly what my stored proc does. (Any new customer purchase order received through Electronic Data Interchange that is placed on designated directory is flagged by an e-mail. The directory is checked periodically for new contents).

You can easily add error reporting for debugging purposes, using the values of @hr returned by the OA method call.

[font="Courier New"]EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(@ks_cdoSendUsingMethod).Value','2'[/font]

[font="Courier New"]IF @hr <>0 BEGIN

INSERT INTO [dbo].[cdosysmail_failures]

(

[Date of Failure], Spid, [From], [To], [Cc], [Subject], [Body],

iMsg, Hr, [Source of Failure], [Description of Failure], [Output from Failure],

[Comment about Failure]

)

VALUES

(

getdate(), @@spid, @ls_From, @ls_To, @ls_Cc, @ls_Subject, @ls_Body,

@iMsg, @hr, @source, @description, @output,

'Failed at sp_OASetProperty sendusing'

)

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0 BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

INSERT INTO [dbo].[cdosysmail_failures]

(

[Date of Failure], Spid, [From], [To], [Cc], [Subject], [Body],

iMsg, Hr, [Source of Failure], [Description of Failure], [Output from Failure],

[Comment about Failure]

)

VALUES

(

getdate(), @@spid, @ls_From, @ls_To, @ls_Cc, @ls_Subject, @ls_Body,

@iMsg, @hr, @source, @description, @output,

'sp_OAGetErrorInfo for sp_OASetProperty sendusing'

)

GOTO send_cdosysmail_cleanup

END

ELSE BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

GOTO send_cdosysmail_cleanup

END

[/font]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Again... very cool and thanks for taking the time.

You should write an article on this... good stuff. I'll be "playing" with all of this all next week. Thanks, again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J-440512 SSCertifiable Points: 6285 More actions · Answer 10

Writing an article ... that would be my first ever.

However, I took all the information from others, such as Desiree Harris. Unfortunately, she had not explained about the weird-looking values of CDOSYS parameters (I do not know anything about SMTP, it took me some time to figure out why the e-mails were apparently never sent but were actually sent to a default directory). So at the beginning I was just suffering in silence, until I got it to work on my own, since my first post on how to work this out did not elicit any response.

Just took someone else's work to make it clearer and make it public by posting it here. I'll readily admit to shamelessly stealing signature lines I like. But I really cannot claim this work as my own.

In any case, do let me know what else you find out about this and the results you have achieved.

I just hope I did not unleash the ultimate spam generator ... certainly was not my intent.