CDONTS usage in a stored procedure

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)

CDONTS usage in a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-11 : 10:09:05

Kevin writes "I am successfully sending emails using CDONTS COM objects in SQL but for some reason the BODY text is being truncated at 255. I have tried VARCHAR(8000) and CHAR(8000) in my declarations but nothing seems to change it. If I set the length below 255 then it makes a difference in the output.

Do you have any suggestions on how I can solve this?

Thanks

CREATE PROCEDURE sp_MailSend

AS

declare @mail_job_id varchar(10)
declare @mail_job_from as VARCHAR(100)
declare @mail_job_to as VARCHAR(100)
declare @mail_job_subject as VARCHAR(150)
declare @mail_job_body as CHAR(5000)
declare @mail_job_footer_text varchar(2000)
declare @mail_job_sub_comment varchar(1000)
--declare @mail_message varchar(8000)
--declare @bond_id int
--declare @email_address varchar(100)
--declare @SiteID varchar(10)
--declare @break varchar(10)
--declare @Mail_Flag int

DECLARE @result INT
DECLARE @object INT

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

set @mail_job_to = 'KevinRussell@spherion.com'
set @mail_job_from = 'KevinRussell@spherion.com'

select
@mail_job_id = Mail_ID,
@mail_job_subject = Subject,
@mail_job_body = Body,
@mail_job_footer_text = Footer_Text,
@mail_job_sub_comment = Sub_Comment

from Mail_Job
where getdate() > send_date
and isnull(status,0) = 0

PRINT 'Sending the message using the Send method'
EXEC @result = sp_OAMethod @object, 'Send', NULL, @mail_job_from, @mail_job_to, @mail_job_subject, @mail_job_body

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

PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object

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

RETURN 0

GO"

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-11 : 14:21:09

Is this SQL7 ??

In SQL 7 sp_OA only supports up to 255 chars max
In SQL 2000 it is 8000 chars

[url]http://support.microsoft.com/support/kb/articles/Q278/4/48.ASP[/url]

Not aware of a workaround in SQL 7

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources