Not able to receive the email from the stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Not able to receive the email from the stored proc

Author

Topic

sreevani
Starting Member

1 Post

Posted - 2006-05-30 : 13:44:17

Hello,
I am having a stored procedure like this

-----
declare @CreatedByAlias varchar(50),
@expireat datetime,
@mailsent char(3),
@status varchar(10)
declare cur CURSOR for
select CreatedByAlias,expireat,mailsent,status
from Issues
where status = 'Open' and
DateDiff(minute,getdate(),expireat) = 30 and
mailsent ='No'
open cur
fetch next from cur into @CreatedByAlias,@expireat,@mailsent,@status
while @@FETCH_STATUS = 0
BEGIN
exec master.dbo.xp_sendmail @recipents=@CreatedByAlias,
@message=N'Reminder !!! This issue should be closed with in 30
minutes.' ;

Update Issues set mailsent ='Yes'
where CreatedByAlias = @CreatedByAlias and
expireat = @expireat and
mailsent = @mailsent and
status = @status

fetch next from cur into @CreatedByAlias,@expireat,@mailsent,@status
END

close cur
deallocate cur
------
I am trying to send mail using xp_sendmail, i can see that the record is getting updated but I am not able to receive any email, can you please tell me why I am not getting the email when this stored procedure is executed.

Thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-31 : 06:03:02

What does the sp say when you run it. There should be an email sent message.
Try displaying the @CreatedByAlias value and running the xp_sendmail independantly.
Does xp_sendmail work on that server?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources