Forums » .NET » SQL Server »

stored proc in sql server to send mail.

Posted Date: 12 Sep 2008      Posted By:: Ujwala     Member Level: Bronze  Member Rank: 0     Points: 1   Responses: 2

I have written stored procedure for sending mail from sql server.
But I m getting result code -2147221005 and my mail is not getting send.
Stored procedure i have written is

ALTER Procedure [dbo].[sp_SMTPMail]

@SenderName varchar(100)='Ujwala',
@SenderAddress varchar(100)='',
@RecipientName varchar(100)='abc',
@RecipientAddress varchar(100)='',
@Subject varchar(200)='hi',
@Body varchar(8000)='Hi,How r u?',
@MailServer varchar(100) = 'localhost'


SET nocount on

declare @oMail int --Object reference
declare @resultcode int
begin try

-- EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
end try
begin catch


end catch
print 'Result Code'
print @resultCode
if @resultcode = 0
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver

EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress

EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress

EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body

EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

EXEC sp_OADestroy @oMail

SET nocount off

will anyone pls tell me what that resultcode is realted to.
Waiting for a favourable reply.
Thankx and Regards.


#294587    Author: vipul      Member Level: Gold      Member Rank: 17     Date: 12/Sep/2008   Rating: 2 out of 52 out of 5     Points: 4

For that you refer this mail they provide in the detail about your error.


Thanks & Regards
Patel Vipul

#294627    Author: Ujwala      Member Level: Bronze      Member Rank: 0     Date: 12/Sep/2008   Rating: 2 out of 52 out of 5     Points: 4

Thankx for your help.
I have gone though this link. But the link in the stored proc which r mention r not getting displayed so i not getting the things.
And i just want to know what the result code -2147221005 is realted to.
Waiting for reply.
Thanks and regards,

Post Reply     

 This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.

  • Return to Discussion Forum
  • Start new thread

  • Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India