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)='ujwala.mahure@bsmart.in',
@RecipientName varchar(100)='abc',
@RecipientAddress varchar(100)='ujwala.mahure@bsmart.in',
@Subject varchar(200)='hi',
@Body varchar(8000)='Hi,How r u?',
@MailServer varchar(100) = 'localhost'

AS

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

print ERROR_MESSAGE()



end catch
print 'Result Code'
print @resultCode
if @resultcode = 0
BEGIN
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
END

SET nocount off

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








Responses

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

hi,
For that you refer this mail they provide in the detail about your error.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=7824&whichpage=5
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649
http://support.microsoft.com/kb/312839/

vipul,
http://dongavipul.blogspot.com

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,
ujwala.


 
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.



Next : Stored procedure to describe all the tables in a database
Previous : reg after insert
Return to Discussion Forum
Post New Message
Category: SQL Server

Related Messages

Subscribe to Subscribers
Active Members
TodayLast 7 Daysmore...

Awards & Gifts
Talk to Webmaster Tony John

Online Members

Tony John
More...
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India