Send an e-mail when an update succeeds/fails.

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

Transact-SQL (2000)

Send an e-mail when an update succeeds/fails.

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-04-15 : 14:28:50

Hello,

I have a stored procedure that updates a table, and I want to receive an e-mail whenever an update is successful or unsuccessful. I don't want to create an update trigger if I don't have to, but rather have the e-mail sent as part of the stored procedure I create. Below is a working version of how the e-mail is sent, but I want to know what was updated/not updated.

Thanks,

D

BEGIN TRANSACTION
update
pl
set
locationprice = p.baseprice
, statusid = p.statusid
from
[dev-svr1].NambePOS.dbo.product p
inner join
[dev-svr1].NambePOS.dbo.productlocation pl
on
p.skuid = pl.skuid
where
p.baseprice <> pl.locationprice
or
p.statusid <> pl.statusid
SELECT
@Upd_Err_POSProdL = @@ERROR
IF
@Upd_Err_POSProdL = 0
BEGIN

EXEC
master.dbo.xp_sendmail
@recipients='email address'
, @subject='Products were successfully updated in NambePOS.ProductLocation'
COMMIT TRANSACTION
END
ELSE
BEGIN
EXEC
master.dbo.xp_sendmail
@recipients='email address'
, @subject='FAILED - Products not successfully updated in NambePOS.ProductLocation'
ROLLBACK TRANSACTION
END

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-15 : 14:49:16

Looks like you need a parameter @body=@message on the EXEC of xp_sendmail?

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-15 : 14:53:39

[code]
DECLARE @message VARCHAR (3000)
SET @message = NULL
update pl
set locationprice = p.baseprice , statusid = p.statusid ,
@message = IsNull(@message + CHR(13), '') + CAST(pl.locationprice AS VARCHAR) +
' changed to: ' + CAST(p.baseprice AS VARCHAR) -- tweak to meet your email needs
from [dev-svr1].NambePOS.dbo.product p
inner join [dev-svr1].NambePOS.dbo.productlocation pl
on p.skuid = pl.skuid
where p.baseprice <> pl.locationprice
or p.statusid <> pl.statusid
[/code]

SQLFarms
Starting Member

1 Post

Posted - 2005-04-15 : 15:08:14

Just wanted to give a heads up: xp_sendmail has memory leak issues.
You'd might want to stay away from it. Instead, it is highly recommended to use different extended stored procs, one such is xp_smtp_sendmail available in the xpsmtp80.dll.
You can look for information on xp_smtp_sendmail on line.

------------------------
SQL Farms Solutions
www.sqlfarms.com

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-15 : 15:53:09

Here's an article on a CDOSYS stored proc.

-- Forgot to mention, gotta have sysadmin permissions to send email with this stored proc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources