E-Mail User When StoredProc Fails

Hi,

I want to e-mail a user when a Stored Proc fails, what is the best way to do this? I was going to create a DTS package or is this too complicated?

Also, the Stored Proc inserts data from one table to another, I would like to use Transactions so that if this fails it rolls back to where it was, I'm not sure of the best way to go about this. Could anyone possibly point me in the right direction? Here's a copy of some of the stored procedure to give an idea of what I am doing:

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn

Any help would me very much appreciated,

ThanksSomething like this should work:

CREATE PROCEDURE ProcName

AS

BEGIN TRANSACTION transaction_1

DECLARE @.error_handle VARCHAR(255)

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Surgery insert.'
GOTO error_handle
END

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_SurgerySlot insert.'
GOTO error_handle
END

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Appointment insert.'
GOTO error_handle
END

-- empty input tables
DELETE FROM tbl_SurgeryIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_Surgery delete.'
GOTO error_handle
END

DELETE FROM tbl_SurgerySlotIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_SurgerySlotIn delete.'
GOTO error_handle
END

DELETE FROM tbl_AppointmentIn

IF @.@.ERROR <> 0
BEGIN
SELECT @.error_handle = 'ProcName::Failure on tbl_AppointmentIn delete.'
GOTO error_handle
END

end_procedure:
COMMIT TRANSACTION transaction_1 --Commits transactions if no errors occurred.
RETURN 0 --Indicates succcess.

error_handle
ROLLBACK TRANSACTION transaction_1
RAISERROR(@.error_handle,16,1)
EXEC xp_sendmail 'user@.mail.com',@.error_handle
RETURN 1

Alternately, if you are running this from a job, you can strip out the xp_sendmail and just have it send email on failure. The RETURN 1 with the RAISERROR will indicate failure.|||I would strongly suggest creating a job to run the stored procedure, and having the job email you on failure. It is easy to do, and relatively foolproof!

-PatP|||Thanks, that's great, I'll give it a go. So the RAISERROR and RETURN 1 are just a way of letting SQL know that the procedure has failed??

The only thing I'm confused/worried about is the best place to put the BEGIN TRANSACTION and the end_procedure code. The stored procedure I've inherited has rather a lot of BEGINS/ENDS so I'm worried about confusing it, here's a copy of the original, any indication you could give me would really help:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sproc_48hrAccess_Upload

AS

SET DATEFORMAT dmy

DECLARE @.practiceCode char(5)
DECLARE @.server varchar(255)
DECLARE @.inPath varchar(255)
DECLARE @.archiveBase varchar(255)
DECLARE @.archivePath varchar(255)
DECLARE @.zipPath varchar(255)
DECLARE @.cmdshell varchar(255)
DECLARE @.result int
DECLARE @.date varchar(10)

SET @.server = 'MURDOCH'
SET @.inPath = 'E:\48hrAccess\48hrDataIn\'
SET @.archiveBase = 'E:\48hrAccess\48hrDataArchive\'
SET @.zipPath = 'C:\Progra~1\WinZip\' --C:\Program Files\Winzip\
SET @.date = CONVERT(varchar(2),DATEPART(dd,GETDATE()))+CONVERT (varchar(2),DATEPART(mm,GETDATE()))+CONVERT(char(4 ),DATEPART(yy,GETDATE()))

-- upload for each practice in tbl_Practice
DECLARE allPractices CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT practiceCode FROM tbl_Practice
OPEN allPractices
FETCH NEXT FROM allPractices INTO @.practiceCode

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.archivePath = @.archiveBase+@.practiceCode+'\'
-- copy files into archive folder
SET @.cmdshell = 'MOVE '+@.inPath+'48hr_'+@.practiceCode+'_'+@.date+'.zip '+@.archivePath
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- unzip file
SET @.cmdshell = @.zipPath+'WZUNZIP -ybc -o -sPASSWORD '+@.archivePath+'48hr_'+@.practiceCode+'_'+@.date+'.z ip '+@.archivePath
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- copy data files into upload tables
SET @.cmdshell = 'ECHO ** BEGIN Upload '+CONVERT(varchar,GETDATE())+' ******************************* >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_Surgery.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_SurgeryIn in '+@.archivePath+'xv_Surgery.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_SurgerySlot.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_SurgerySlotIn in '+@.archivePath+'xv_SurgerySlot.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

SET @.cmdshell = 'ECHO xv_Appointment.dat >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT
SET @.cmdshell = 'bcp GMS_48hrAccess..tbl_AppointmentIn in '+@.archivePath+'xv_Appointment.dat -n -V65 -t"||" -r"|||\n" -S'+@.server+' -T >> '+@.archivePath+'48hrUpload.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- clean up
SET @.cmdshell = 'DEL /F '+@.archivePath+'*.dat '+@.archivePath+'48hrAccess.log'
EXEC master..xp_cmdshell @.cmdshell, NO_OUTPUT

-- update tbl_SurgerySlotDescription (will fire trigger if new labels appear)
INSERT INTO tbl_SurgerySlotDescription
SELECT DISTINCT @.practiceCode, ssi.Label, ssi.AutoFillMessage, ssi.IsBookable, null
FROM tbl_SurgerySlotIn ssi
WHERE SUBSTRING(ssi.SurgerySlotKey,PATINDEX('%.%',ssi.Su rgerySlotKey)+1,5) = @.practiceCode
AND ssi.Label NOT IN (
SELECT Label
FROM tbl_SurgerySlotDescription
WHERE PracticeCode = @.practiceCode
)

-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@.practiceCode --SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn

INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@.practiceCode
FROM tbl_SurgerySlotIn

INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn

-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn

FETCH NEXT FROM allPractices INTO @.practiceCode
END

CLOSE allPractices
DEALLOCATE allPractices

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO