Difference between revisions of "Dbo.uspLogError (procedure)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspLogError |- |} <pre> -- uspLogError logs error information in the ...)
 
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- valign="top"
 +
| '''Procedure
 +
| dbo.uspLogError
 +
|- valign="top"
 +
| '''Description
 +
| Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.
 +
|}
 +
 +
=== Source ===
 +
<pre>
 +
-- uspLogError logs error information in the ErrorLog table about the
 +
-- error that caused execution to jump to the CATCH block of a
 +
-- TRY...CATCH construct. This should be executed from within the scope
 +
-- of a CATCH block otherwise it will return without inserting error
 +
-- information.
 +
CREATE PROCEDURE [dbo].[uspLogError]
 +
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
 +
AS                              -- by uspLogError in the ErrorLog table
 +
BEGIN
 +
    SET NOCOUNT ON;
 +
 +
    -- Output parameter value of 0 indicates that error
 +
    -- information was not logged
 +
    SET @ErrorLogID = 0;
 +
 +
    BEGIN TRY
 +
        -- Return if there is no error information to log
 +
        IF ERROR_NUMBER() IS NULL
 +
            RETURN;
 +
 +
        -- Return if inside an uncommittable transaction.
 +
        -- Data insertion/modification is not allowed when
 +
        -- a transaction is in an uncommittable state.
 +
        IF XACT_STATE() = -1
 +
        BEGIN
 +
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
 +
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
 +
            RETURN;
 +
        END
 +
 +
        INSERT [dbo].[ErrorLog]
 +
            (
 +
            [UserName],
 +
            [ErrorNumber],
 +
            [ErrorSeverity],
 +
            [ErrorState],
 +
            [ErrorProcedure],
 +
            [ErrorLine],
 +
            [ErrorMessage]
 +
            )
 +
        VALUES
 +
            (
 +
            CONVERT(sysname, CURRENT_USER),
 +
            ERROR_NUMBER(),
 +
            ERROR_SEVERITY(),
 +
            ERROR_STATE(),
 +
            ERROR_PROCEDURE(),
 +
            ERROR_LINE(),
 +
            ERROR_MESSAGE()
 +
            );
 +
 +
        -- Pass back the ErrorLogID of the row inserted
 +
        SET @ErrorLogID = @@IDENTITY;
 +
    END TRY
 +
    BEGIN CATCH
 +
        PRINT 'An error occurred in stored procedure uspLogError: ';
 +
        EXECUTE [dbo].[uspPrintError];
 +
        RETURN -1;
 +
    END CATCH
 +
END;
 +
</pre>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Insert
 +
| Table
 +
| [[dbo.ErrorLog_(table)|dbo.ErrorLog]]
 +
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
|}
 +
 +
=== Dependencies ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
| '''Child Type
 +
| '''Child Object
 +
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[HumanResources.uspUpdateEmployeeHireInfo_(procedure)|HumanResources.uspUpdateEmployeeHireInfo]]
 +
 +
|
 +
|
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[HumanResources.uspUpdateEmployeeLogin_(procedure)|HumanResources.uspUpdateEmployeeLogin]]
 +
 +
|
 +
|
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[HumanResources.uspUpdateEmployeePersonalInfo_(procedure)|HumanResources.uspUpdateEmployeePersonalInfo]]
 +
 +
|
 +
|
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Production.WorkOrder_(table)|Production.WorkOrder]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Production.WorkOrder_(table)|Production.WorkOrder]]
 +
 +
| Trigger
 +
| uWorkOrder
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| uPurchaseOrderDetail
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 +
 +
| Trigger
 +
| uPurchaseOrderHeader
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Purchasing.Vendor_(table)|Purchasing.Vendor]]
 +
 +
| Trigger
 +
| dVendor
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 +
 +
| Trigger
 +
| iduSalesOrderDetail
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 +
|- valign="top"
 +
| Execute
 +
| Table
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
 +
| Trigger
 +
| iStore
 +
|}
 +
 +
 +
== automatically generated ==
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 5: Line 196:
 
| '''procedure
 
| '''procedure
 
| dbo.uspLogError
 
| dbo.uspLogError
 +
|- valign="top"
 +
| '''Description
 +
| Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.
 
|-
 
|-
 
|}
 
|}
  
 
<pre>
 
<pre>
 +
 
-- uspLogError logs error information in the ErrorLog table about the  
 
-- uspLogError logs error information in the ErrorLog table about the  
 
-- error that caused execution to jump to the CATCH block of a  
 
-- error that caused execution to jump to the CATCH block of a  
Line 70: Line 265:
 
END;
 
END;
 
</pre>
 
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| Insert
 +
| Table
 +
| [[dbo.ErrorLog_(table)|dbo.ErrorLog]]
 +
 +
|-
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
|}
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
| '''Child Type
 +
| '''Child Object
 +
 +
|-
 +
| Execute
 +
| Procedure
 +
| [[HumanResources.uspUpdateEmployeeHireInfo_(procedure)|HumanResources.uspUpdateEmployeeHireInfo]]
 +
 +
|
 +
|
 +
|-
 +
| Execute
 +
| Procedure
 +
| [[HumanResources.uspUpdateEmployeeLogin_(procedure)|HumanResources.uspUpdateEmployeeLogin]]
 +
 +
|
 +
|
 +
|-
 +
| Execute
 +
| Procedure
 +
| [[HumanResources.uspUpdateEmployeePersonalInfo_(procedure)|HumanResources.uspUpdateEmployeePersonalInfo]]
 +
 +
|
 +
|
 +
|-
 +
| Execute
 +
| Table
 +
| [[Production.WorkOrder_(table)|Production.WorkOrder]]
 +
 +
| Trigger
 +
| iWorkOrder
 +
|-
 +
| Execute
 +
| Table
 +
| [[Production.WorkOrder_(table)|Production.WorkOrder]]
 +
 +
| Trigger
 +
| uWorkOrder
 +
|-
 +
| Execute
 +
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|-
 +
| Execute
 +
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| uPurchaseOrderDetail
 +
|-
 +
| Execute
 +
| Table
 +
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 +
 +
| Trigger
 +
| uPurchaseOrderHeader
 +
|-
 +
| Execute
 +
| Table
 +
| [[Purchasing.Vendor_(table)|Purchasing.Vendor]]
 +
 +
| Trigger
 +
| dVendor
 +
|-
 +
| Execute
 +
| Table
 +
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 +
 +
| Trigger
 +
| iduSalesOrderDetail
 +
|-
 +
| Execute
 +
| Table
 +
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 +
|-
 +
| Execute
 +
| Table
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
 +
| Trigger
 +
| iStore
 +
|}

Latest revision as of 00:15, 24 June 2010

wikibot[edit]

Procedure dbo.uspLogError
Description Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.

Source[edit]

-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;

References[edit]

Dependency Type Object Type Referenced Object
Insert Table dbo.ErrorLog
Execute Procedure dbo.uspPrintError

Dependencies[edit]

Reference Type Object Type Referencing Object Child Type Child Object
Execute Procedure HumanResources.uspUpdateEmployeeHireInfo
Execute Procedure HumanResources.uspUpdateEmployeeLogin
Execute Procedure HumanResources.uspUpdateEmployeePersonalInfo
Execute Table Production.WorkOrder Trigger iWorkOrder
Execute Table Production.WorkOrder Trigger uWorkOrder
Execute Table Purchasing.PurchaseOrderDetail Trigger iPurchaseOrderDetail
Execute Table Purchasing.PurchaseOrderDetail Trigger uPurchaseOrderDetail
Execute Table Purchasing.PurchaseOrderHeader Trigger uPurchaseOrderHeader
Execute Table Purchasing.Vendor Trigger dVendor
Execute Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Execute Table Sales.SalesOrderHeader Trigger uSalesOrderHeader
Execute Table Sales.Store Trigger iStore


automatically generated[edit]

procedure dbo.uspLogError
Description Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information. 

-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;


Dependency Type Object Type Referenced Object
Insert Table dbo.ErrorLog
Execute Procedure dbo.uspPrintError


Reference Type Object Type Referencing Object Child Type Child Object
Execute Procedure HumanResources.uspUpdateEmployeeHireInfo
Execute Procedure HumanResources.uspUpdateEmployeeLogin
Execute Procedure HumanResources.uspUpdateEmployeePersonalInfo
Execute Table Production.WorkOrder Trigger iWorkOrder
Execute Table Production.WorkOrder Trigger uWorkOrder
Execute Table Purchasing.PurchaseOrderDetail Trigger iPurchaseOrderDetail
Execute Table Purchasing.PurchaseOrderDetail Trigger uPurchaseOrderDetail
Execute Table Purchasing.PurchaseOrderHeader Trigger uPurchaseOrderHeader
Execute Table Purchasing.Vendor Trigger dVendor
Execute Table Sales.SalesOrderDetail Trigger iduSalesOrderDetail
Execute Table Sales.SalesOrderHeader Trigger uSalesOrderHeader
Execute Table Sales.Store Trigger iStore
Retrieved from "http://dbshelp.devio.at/index.php?title=Dbo.uspLogError_(procedure)&oldid=2593"