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
Jungalist wrote:
gt1329a: