Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Monday, May 23, 2011

Stored procedure to get Original error



USE [DBNAME]
GO
/****** Object:  StoredProcedure [dbo].[f9001_pcrtw_Error]    Script Date: 05/24/2011 12:13:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Author   :Mahadevan
-- Create date  :09/06/2010
-- Description  :The procedure rethrows the original error
-- Modified Date :Modified By   Reason
-- =====================================================================
CREATE PROCEDURE [dbo].[f9001_pcrtw_Error]
AS
 BEGIN
 SET NOCOUNT ON
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL
        RETURN;
    DECLARE @ErrorMessage    NVARCHAR(4000)
    DECLARE @ErrorNumber     INT
    DECLARE @ErrorSeverity   INT
    DECLARE @ErrorState      INT
    DECLARE @ErrorLine       INT
    DECLARE @ErrorProcedure  NVARCHAR(200)
    -- Assign variables to error-handling functions that
    -- capture information for RAISERROR.
    SELECT @ErrorNumber = ERROR_NUMBER(),
   @ErrorSeverity = ERROR_SEVERITY(),
   @ErrorState = ERROR_STATE(),
   @ErrorLine = ERROR_LINE(),
   @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +'Message: '+ ERROR_MESSAGE();
 
    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR
    (
  @ErrorMessage,
  @ErrorSeverity,
  1,              
  @ErrorNumber,    -- parameter: original error number.
  @ErrorSeverity,  -- parameter: original error severity.
  @ErrorState,     -- parameter: original error state.
  @ErrorProcedure, -- parameter: original error procedure name.
  @ErrorLine       -- parameter: original error line number.
    )
 SET NOCOUNT OFF
 END
GO

Use the procedure "f9001_pcrtw_Error" along with other stored procedure as

CREATE PROCEDURE <name>
AS
BEGIN
  BEGIN TRY
   <SQL Statement>
  END TRY

 BEGIN CATCH
   EXEC f9001_pctw_Error
 END CATCH

END







 

No comments:

Post a Comment