dbo.uspLogError Stored Procedure

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.

Properties

Creation Date	08/01/2010 08:40
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@ErrorLogID	Out	Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.	int	4
@RETURN_VALUE	Return Value		int	4

Objects that depend on dbo.uspLogError

Database Object	Object Type	Description	Dep Level
dVendor	Trigger	INSTEAD OF DELETE trigger which keeps Vendors from being deleted.	2
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	2
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	2
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	2
iStore	Trigger	AFTER INSERT trigger inserting Store only if the Customer does not exist in the Individual table.	2
iuIndividual	Trigger	AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Individual table to the current date.	3
iWorkOrder	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table.	2
Purchasing.ProductVendor	Table	Cross-reference table mapping vendors with the products they supply.	2
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	1
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	1
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	1
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	2
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
Sales.StoreContact	Table	Cross-reference table mapping stores and their employees.	2
dbo.ufnGetContactInformation	User Defined Function	Table value function returning the first name, last name, job title and contact type for a given contact.	3
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	2
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	2
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	2
HumanResources.uspUpdateEmployeeHireInfo	Stored Procedure	Updates the Employee table and inserts a new row in the EmployeePayHistory table with the values specified in the input parameters.	1
HumanResources.uspUpdateEmployeeLogin	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	1
HumanResources.uspUpdateEmployeePersonalInfo	Stored Procedure	Updates the Employee table with the values specified in the input parameters for the given EmployeeID.	1
uWorkOrder	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.	2
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1
Purchasing.VendorAddress	Table	Cross-reference mapping vendors and addresses.	2
Purchasing.VendorContact	Table	Cross-reference table mapping vendors and their employees.	2
Sales.vIndividualCustomer	View	Individual customers (names and addresses) that purchase Adventure Works Cycles products online.	3
Sales.vIndividualDemographics	View	Displays the content from each element in the xml column Demographics for each customer in the Sales.Individual table.	3
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	2
Sales.vStoreWithDemographics	View	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.	2
Purchasing.vVendor	View	Vendor (company) names and addresses and the names of vendor employees to contact.	2
Production.WorkOrder	Table	Manufacturing work orders.	1
Production.WorkOrderRouting	Table	Work order details.	2

Objects that dbo.uspLogError depends on

	Database Object	Object Type	Description	Dep Level
	dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	1
	dbo.uspPrintError	Stored Procedure	Prints error information 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 printing any error information.	1

Procedure Source Code

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;
SET @ErrorLogID = 0;
BEGIN TRY
IF ERROR_NUMBER() IS NULL
RETURN;
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()
);
SET @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT 'An error occurred in stored procedure uspLogError: ';
EXECUTE [dbo].[uspPrintError];
RETURN -1;
END CATCH
END;

Related Objects

dbo Schema
AdventureWorks Database