how to create a stored procedure to send mail xp_send_mail...? : The Official Microsoft ASP.NET Forums

Reply

vijay_babu

Member

319 Points

how to create a stored procedure to send mail xp_send_mail...?

Dec 16, 2008 05:36 AM|LINK

hi !
i have to send mail with link to the user who are all registered. this process is completed and i stored the information in sqlserver. but if the user didnt accept the link for three days from the sent date then have to send a reminder message as a mail to the user ! how to send the reminder message as a mail...? am working in vb.net backend sqlserver 2005
if anyone have a idea of solving this pls give me some idea !!

the reply for this is

1. You will first need to setup SQL Server so that it can send emails. Search the web for some tutorials on how to do this.
2. The stored procedure to send emails is master..xp_send_mail.
3. Create a stored procedure that accesses your tables and, if the todays date is right, creates and sends the emails.
4. Finally create a Job that runs the stored procedure at the required times. (In SQL 2000 this is located in Management -> SQL Server Agent -> Jobs)

first step completed ! what have to do for second step...?

Reply

SATISD9X

Contributor

2713 Points

487 Posts

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 16, 2008 09:08 AM|LINK

Browse thru the following articles:

-Sending mails
http://www.sqlservercentral.com/articles/SQLMail/sqlmailusingpop3andsmtp/1577/
http://support.microsoft.com/kb/312839

-Sending mails through SP's
http://www.dotnetspider.com/resources/19638-Sending-email-through-sql-server-stored-procedure.aspx

-Conditionally send email when date is reached
http://www.developerbarn.com/sql-development/206-if-statement-stored-procedure.html

Satish Chilkury
MCTS .Net Framework 2.0 Web Applications
Web Developer 3.5

~ Please Mark as Answer if it solves your query ~

Reply

mudassarkhan

All-Star

78956 Points

13402 Posts

MVP

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 16, 2008 09:25 AM|LINK

Hi

Use the following stored proc to send email from SQL server 2000

but since u r using SQL server 2005 u will have to rub the following script to enable OLE Automation Procedures since by default they r disabled in sql server 2005

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then create the stored proc

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

@bodytype varchar(10),@output_desc varchar(1000) output,@output_mesg varchar(10) output

AS

DECLARE @imsg int DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',

'smtp.xxx.com'

--UserName

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',

'abc@xxx.com'

--Password

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',

'xxxxxx'

--UseSSL True/False

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',

'True'

--PORT

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',

'Port No''

--Requires Aunthentication None(0) / Basic(1)

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',

'1'

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.

IF @hr <>0 SELECT @hr

BEGIN

EXEC @hr = sp_oageterrorinfo null, @source out, @description out

IF @hr = 0

BEGIN

--set @output_desc = ' source: ' + @source

set @output_desc = @description

END

ELSE

BEGIN

SET @output_desc = ' sp_oageterrorinfo failed'

END

IF not @output_desc is NULL

SET @output_mesg = 'Error'

END

EXEC @hr = sp_oadestroy @imsg

To execute the stored procedure do the following

DECLARE @out_desc varchar(1000),

@out_mesg varchar(10)

EXEC sp_send_cdosysmail @From,@To,@Subject,@Body,@BodyType, @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

Depending on your mail settings you can use the above stored procedure to send email using smtp server

And u can schedule this sp in SQL server schedular to run on daily basis

Thats it

MAK [MVP ASP.Net] ASP.Net Freelancer

Contact me

Edited by mudassarkhan on Dec 16, 2008 09:28 AM

Reply

vijay_babu

Member

319 Points

165 Posts

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 17, 2008 10:39 AM|LINK

can you teel me that the following stored procedure is correct ?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create Procedure [dbo].[sp_SMTPMail]
    @SenderName varchar(100),
    @SenderAddress varchar(100),
    @RecipientName varchar(100),
    @RecipientAddress varchar(100),
    @Subject varchar(200),
    @Body varchar(8000)
AS
    SET nocount on

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

    EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT

    if @resultcode = 0
    BEGIN
        EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
        EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
        EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
        EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body

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

        EXEC sp_OADestroy @oMail
    END


    SET nocount off

for execution

exec sp_SMTPMail @SenderName='sender', @SenderAddress='sender@mail.com',
@RecipientName = 'recipient', @RecipientAddress = 'receipient@mail.com',
@Subject='SQL Mail Test',
@body='This is a test message from SQL Server. Smile! It worked.'

Edited by vijay_babu on Dec 17, 2008 11:18 AM

Reply

mudassarkhan

All-Star

78956 Points

13402 Posts

MVP

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 17, 2008 11:02 AM|LINK

Hi Vijay

y r u not using my stored proc it works perfectly

to make it run in SQL server 2005 u will have to run this script as i said u before

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then create my stored proc for email

MAK [MVP ASP.Net] ASP.Net Freelancer

Contact me

Marked as answer by vijay_babu on Dec 18, 2008 10:41 AM

Reply

vijay_babu

Member

319 Points

165 Posts

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 17, 2008 11:26 AM|LINK

hi !

First i have run your stored procedure before that i run

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

 output is succesful

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.

while run stored procedure

output is

Msg 102, Level 15, State 1, Procedure sp_send_cdosysmail, Line 53
Incorrect syntax near 'configuration'.
Msg 105, Level 15, State 1, Procedure sp_send_cdosysmail, Line 97
Unclosed quotation mark after the character string '

END

EXEC @hr = sp_oadestroy @imsg
'.

pls identify my mistake and reply me...!

Reply

mudassarkhan

All-Star

78956 Points

13402 Posts

MVP

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 17, 2008 11:41 AM|LINK

U failed to remove my comments thats y

use this

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]

@from varchar(500) ,

@to varchar(500) ,

@subject varchar(500),

@body varchar(4000) ,

@bodytype varchar(10),@output_desc varchar(1000) output,@output_mesg varchar(10) output

AS

DECLARE @imsg int DECLARE @hr int

DECLARE @source varchar(255)

DECLARE @description varchar(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

--SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,

'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

--SMTP Server

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value',

'smtp.xxx.com'

--UserName

exec @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").value',

'abc@xxx.com'

--Password

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").value',

'xxxxxx'

--UseSSL True/False

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl").value',

'True'

--PORT

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").value',

'Port No'

--Requires Aunthentication None(0) / Basic(1)

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value',

'1'

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null

EXEC @hr = sp_oasetproperty @imsg, 'to', @to

EXEC @hr = sp_oasetproperty @imsg, 'from', @from

EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body

EXEC @hr = sp_oamethod @imsg, 'send', null

SET @output_mesg = 'Success'

-- sample error handling.

IF @hr <>0 SELECT @hr

BEGIN

EXEC @hr = sp_oageterrorinfo null, @source out, @description out

IF @hr = 0

BEGIN

--set @output_desc = ' source: ' + @source

set @output_desc = @description

END

ELSE

BEGIN

SET @output_desc = ' sp_oageterrorinfo failed'

END

IF not @output_desc is NULL

SET @output_mesg = 'Error'

END

EXEC @hr = sp_oadestroy @imsg

MAK [MVP ASP.Net] ASP.Net Freelancer

Contact me

Reply

vijay_babu

Member

319 Points

165 Posts

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 17, 2008 12:06 PM|LINK

hi !

all run perfect execpt one thing final execution

EXEC sp_send_cdosysmail @From,@To,@Subject,@Body,@BodyType, @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

output message is

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@From".
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@out_mesg".

one more question number of stored procedures are used in your query but where the stored procedures are stored or where i have to store it can you tell me ? pls dnt feel guilty to reply me ! i have no other way to go

Edited by vijay_babu on Dec 17, 2008 12:11 PM

Reply

mudassarkhan

All-Star

78956 Points

13402 Posts

MVP

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 17, 2008 12:24 PM|LINK

EXEC sp_send_cdosysmail 'Youremail','To email, 'Subject'','Body','HTMLbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

they are all system stored procs in SQL server

MAK [MVP ASP.Net] ASP.Net Freelancer

Contact me

Reply

vijay_babu

Member

319 Points

165 Posts

Re: how to create a stored procedure to send mail xp_send_mail...?

Dec 18, 2008 03:47 AM|LINK

hi !

have executed the

EXEC sp_send_cdosysmail 'vijaybabu@ace-labs.com','vijay.playful@gmail.com', 'Subject','Body','HTMLbody', @output_desc = @out_desc output, @output_mesg = @out_mesg output

PRINT @out_mesg

error is

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@out_desc".
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@out_mesg".

can u reply me...?

how to create a stored procedure to send mail xp_send_mail...? RSS

30 replies

vijay_babu

how to create a stored procedure to send mail xp_send_mail...?

SATISD9X

Re: how to create a stored procedure to send mail xp_send_mail...?

mudassarkhan

Re: how to create a stored procedure to send mail xp_send_mail...?

vijay_babu

Re: how to create a stored procedure to send mail xp_send_mail...?

mudassarkhan

Re: how to create a stored procedure to send mail xp_send_mail...?

vijay_babu

Re: how to create a stored procedure to send mail xp_send_mail...?

mudassarkhan

Re: how to create a stored procedure to send mail xp_send_mail...?

vijay_babu

Re: how to create a stored procedure to send mail xp_send_mail...?

mudassarkhan

Re: how to create a stored procedure to send mail xp_send_mail...?

vijay_babu

Re: how to create a stored procedure to send mail xp_send_mail...?

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft