October 17, 2008 at 5:24 am
Hi All,
I want to create whte store procedure which should send mail
i m using sp_AoCreate().
nut nt able.It is returning -2147221005
can any one help me
IF EXISTS (SELECT NAME FROM sysobjects
WHERE name = 'sp_SMTPMail' AND type = 'P')
DROP PROCEDURE sp_SMTPMail
GO
Create Procedure sp_SMTPMail
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = mymailserver
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
--EXEC @resultcode = sp_OACreate 'SMTP.mal.comapnyname, @oMail OUT
EXEC @resultcode = sp_OACreate 'SqlMsmq', @oMail OUT
print('sp_OACreate' )
print(@oMail)
print( @resultcode)
if @resultcode =0
BEGIN
print('Seeting Property')
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
print('Seted Property')
print('Sending mail')
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL,@MailServer
print(@resultcode)
print(@resultcode)
EXEC sp_OADestroy @oMail
print('Sent mail')
END
print('Completed')
SET nocount off
Go
exec sp_SMTPMail @SenderName='prayadav', @SenderAddress='prayadav@deloitte.com',
@RecipientName = 'prayadav@deloitte.com', @RecipientAddress = 'prayadav@deloitte.com',
@Subject='SQL Test', @Body='Hello, this is a test email from SQL Server'
October 17, 2008 at 1:25 pm
October 17, 2008 at 1:41 pm
If you are really using SQL Server 2005 you non longer need Outlook to send mail. You use Database Mail and the procedure sp_send_dbmail. Database Mail uses SMTP. You can find out how to set it up and use it in BOL.
If you are not using SQL 2005 you have posted in the wrong forum. In SQL 7, 2000 I recommend using xp_smtp_sendmail which is available at http://sqldev.net/xp/xpsmtp.htm,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply