Send Mail Using 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

Transact-SQL (2000)

Send Mail Using Stored Procedure

Author

Topic

wafi
Starting Member

12 Posts

Posted - 2004-03-10 : 08:51:02

Hi all,,

Iam trying to Send mail using CDO.NewMail in a stored procedure,
it works correctly but the problem is if the Body message length is more than 4000 chars it will send only the first 4000 chars

Anyone cuold help plz

Best Regards

Wafi Mohtaseb

Wafi Mohtaseb

despy
Starting Member

9 Posts

Posted - 2004-03-11 : 04:22:08

My guess would be you're using a nvarchar field at some point which has a maximum of 4000 characters (8000 bytes @ 2 bytes per characters). Do you want to post the code you're using?

wafi
Starting Member

12 Posts

Posted - 2004-03-11 : 05:43:09

Thanks i wil try the nvarcharand i will see it it works
any way here is the code i used im my Stored procedure

CREATE PROCEDURE SEND_EMAIL_FOR_UNACTIVATED_CANDIDATES

AS

DECLARE @FROM VARCHAR(100)
SET @FROM = 'WebMaster@Test.com'

DECLARE @TO VARCHAR(100)
DECLARE @SUBJECT VARCHAR(100)
SET @SUBJECT = 'Reminder to activate your account!'

--#######################################################
/*
Get The Message Body From Email_Template
*/
DECLARE @LOGO VARCHAR(200)
SET @LOGO = 'logo'
DECLARE @Original_Msg VARCHAR(8000)

DECLARE EMAIL_TEMP CURSOR FOR SELECT Template_Text
FROM Email_Templates WHERE Email_Type = 1

OPEN EMAIL_TEMP
FETCH NEXT FROM EMAIL_TEMP INTO @Original_Msg
CLOSE EMAIL_TEMP
DEALLOCATE EMAIL_TEMP
SET @Original_Msg = REPLACE(@Original_Msg,'_logo_',@LOGO)
--#######################################################

DECLARE @BODY VARCHAR(8000)

DECLARE @MAILID INT
DECLARE @HR INT

DECLARE @BODYFORMAT INT
DECLARE @MAILFORMAT INT

SET @BODYFORMAT = 0
SET @MAILFORMAT = 0

DECLARE @CAND_ID INT
DECLARE @UserName VARCHAR(100)
DECLARE @Password VARCHAR(100)
DECLARE @Last_Name VARCHAR(100)
--Get candidates who dose nto complete there registration
DECLARE CAND CURSOR FOR
SELECT Candidate_Id,Email_Address,Username,Password,Last_Name FROM CANDIDATES
WHERE Status_Id = 1
AND DATEDIFF(DAY,Email_Date,GETDATE()) >= 7
AND Send_Email = 1

OPEN CAND
FETCH NEXT FROM CAND INTO @CAND_ID,@TO,@UserName,@Password,@Last_Name

WHILE @@FETCH_STATUS = 0
BEGIN

--Mail Body Settingd
SET @BODY = @Original_Msg
SET @BODY = REPLACE(@BODY,'_LastName_', ' ' +@Last_Name+' ')
SET @BODY = REPLACE(@BODY,'_username_', ' ' +@username+' ')
SET @BODY = REPLACE(@BODY, '_password_', ' ' +@Password +' ')

--Create CDO Mail Com object and set its properites
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, 'Subject', @SUBJECT
EXEC @HR = sp_OASetProperty @MAILID, 'BodyFormat', @BODYFORMAT
EXEC @HR = sp_OASetProperty @MAILID, 'MailFormat', @MAILFORMAT
EXEC @HR = sp_OASetProperty @MAILID, 'To', @TO
EXEC @HR = sp_OAMethod @MAILID, 'Send', NULL
EXEC @HR = sp_OADestroy @MAILID

UPDATE Candidates SET Email_Date = GETDATE() + 7
WHERE Candidate_Id = @CAND_ID
PRINT @BODY

FETCH NEXT FROM CAND INTO @CAND_ID,@TO,@UserName,@Password,@Last_Name
END

CLOSE CAND
DEALLOCATE CAND

Wafi Mohtaseb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources