Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Question Unanswered: 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,

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Something 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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •