Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Pass Through Query to SQL Server - Trapping Errors

    I have a pass through stored procedure that has no return value. It essentially does a bulk import from a text file into a temp table. Massages the data, moves it into a static table, moves duplicates into another table, and then crunches some numbers and puts some aggregate data in to a third table.

    So far, 95% of the time it runs successfully. My question is - how can I notify the user of any issues if the import fails? I have log messages set to true, but the information I get back isn't really useful.

    I tried to set this stored procedure up so that it returns records, but when I did, I kept getting ODBC time outs?!?

    Any help is greatly appreciated.

    Thankss
    sb

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's how I handle such situations:

    1. I create a table in the database on the server:
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Tbl_Log_Procedures](
    	[SysCounter] [int] IDENTITY(1,1) NOT NULL,
    	[Procedure_Name] [nvarchar](255) NOT NULL,
    	[Error_Code] [int] NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Error_Code]  DEFAULT (''),
    	[Error_Message] [nvarchar](2048) NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Error_Message]  DEFAULT (''),
    	[Log_User] [nvarchar](127) NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Log_User]  DEFAULT (suser_sname()),
    	[Log_Time] [datetime] NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Log_Time]  DEFAULT (getdate()),
    	[Log_Code] [int] NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Log_Code]  DEFAULT ((0)),
    	[Log_Message] [nvarchar](1024) NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Log_Message]  DEFAULT (''),
    	[Log_Credentials] [int] NOT NULL CONSTRAINT [DF_Tbl_Log_Procedures_Log_Credentials]  DEFAULT ((0)),
     CONSTRAINT [PK_Tbl_Log_Procedures] PRIMARY KEY CLUSTERED 
    (
    	[SysCounter] ASC
    )WITH (PAD_INDEX  = OFF, 
           STATISTICS_NORECOMPUTE  = OFF, 
           IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS  = ON, 
           ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    2. I have a stored procedure used to write into the table Tbl_Log_Procedures:
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ----------------------------------------------------------------------
    -- Database:     ScoreBoard
    -- Objet:        PROCEDURE [dbo].[Proc_Log_ProcedureEvent]
    -- Author:       SinnDHo
    -- Date:         2011-02-25 00:19:47
    -- Modification: 2011-02-26 12:35:03 (Renamed from Proc_Event_Logger)
    ----------------------------------------------------------------------
    CREATE PROCEDURE [dbo].[Proc_Log_ProcedureEvent]
    (
          @ProcedureName NVARCHAR(127)
        , @ErrorCode INT = 0
        , @ErrorMessage NVARCHAR(2048) = ''
        , @LogCode INT = 0
        , @LogCredentials INT = 0
        , @LogMessage NVARCHAR(1024) = ''
    )
    AS
    BEGIN
        BEGIN TRY
    		INSERT INTO [Tbl_Log_Procedures] 
                ( [Procedure_Name]
    	    , [Error_Code]
                , [Error_Message]
    	    , [Log_Code]
                , [Log_Credentials]
    	    , [Log_Message] )
    		VALUES 
                ( @ProcedureName
    	    , @ErrorCode
                , @ErrorMessage
                , @LogCode
                , @LogCredentials
                , @LogMessage );
        END TRY
        BEGIN CATCH
     		INSERT INTO [Tbl_Log_Procedures] 
                ( [Procedure_Name]
                , [Error_Code]
                , [Error_Message] )
    		VALUES 
                ( 'Proc_Log_ProcedureEvent'
    	    , ERROR_NUMBER()
                , ERROR_MESSAGE() );
        END CATCH
    END
    3. In every strored procedure, I use a CATCH block:
    Code:
        BEGIN CATCH
            SET @ErrorMsg = ERROR_MESSAGE() 
            SET @ReturnValue = ERROR_NUMBER() 
            SET @Status = 0
            EXEC Proc_Log_ProcedureEvent 'Proc_Tbl_Areas_Activate_Ex', @ReturnValue , @ErrorMsg
        END CATCH
    4. After calling a stored procedure from the front-end, it's easy to use a pass-through query to retrieve the error line from the log table.
    Have a nice day!

Posting Permissions

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