How do I send an email from a stored procedure

[vanisudha2000]

Hello,
I am having a stored procedure which is like this below,
How can I send an email from this stored procedure
Can you please help me with the code ...

Thanks in advance...

CREATE PROCEDURE [dbo].[SendEmail]
As
BEGIN
select Alias,ExpireAt,CreatedAt,(completetime*60 - 30)
From Issues
Where status = 'Open' and
CreatedAt < ExpireAt

--!!!! HERE I WANT TO SEND EMAIL TO THE ALIAS SELECTED WITH SOME MESSAGE ----!!!!


END

 

[philhege]

Assuming the right privileges to used extended stored procedures, you can call xp_sendmail at this point.

Read BOL for the parameters.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[vanisudha2000]

I have added these lines in the above stored procedure .
EXEC Issues.dbo.xp_sendmail
@recipients=Alias,
@message=N'Reminder !!! This issue should be closed with in 30 minutes.' ;

I am getting this error,

Cannot add rows to sysdepends for the current object because it depends on the missing object 'Issues.dbo.xp_sendmail'. The object will still be created.

 

[mrdenny]

xp_sendmail is in master, not in your database. Change it to master.dbo.xp_sendmail.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vanisudha2000]

Thankyou, now I am not getting that error.

Now my problem is I am having a column in the database which takes hours like 1, 2,3 integer values.

I have written a stored procedure like this

CREATE PROCEDURE [dbo].[SendEmail]
As
BEGIN
SET NOCOUNT ON;
Select Alias,ExpireAt,CreatedAt,(completetime*60 - 30)
From Issues
Where status = 'Open' and
CreatedAt < ExpireAt

EXEC Master.dbo.xp_sendmail
@recipients=Alias,
@message=N'Reminder !!! This issue should be closed with in 30 minutes.' ;
END

I wanted to send an email to the alias when it is 30minutes before the completiontime if completiontime is 1hour then i want to send the email at 30 minutes before the end of 1hour.
createdat = getdate(), when the issue was created.
expiredat = createdat + completetime.

so can you please correct my stored procedure, and give me the correct one, so that I can send the email exactly before 30 minutes of the completetime or the expiredat.

Thanks in advance

 

[mrdenny]

You would need to use a cursor to open the record set, and then send one email for each record in the record set. Something like this.

declare @Alias varchar(50)
declare cur CURSOR for select Alias
from Issues
where status = 'Open' and CreatedAt < ExpireAt
open cur
fetch next from cur into @Alias
while @@FETCH_STATUS = 0
BEGIN
    exec master.dbo.xp_sendmail @recipents=@Alias,
        @message=N'Reminder !!! This issue should be closed with in 30 minutes.' ;
    fetch next from cur into @Alias
END
close cur
deallocate cur

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com