Table: dbo.ErrorLog

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.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[ContactType](
[ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModelIllustration](
[ProductModelID] [int] NOT NULL,
[IllustrationID] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModelProductDescriptionCulture

Cross-reference table mapping product descriptions and the language the description is written in.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductProductPhoto](
[ProductID] [int] NOT NULL,
[ProductPhotoID] [int] NOT NULL,
[Primary] [dbo].[Flag] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductReview

Customer reviews of products they have purchased.

Fields

Field name

Data type

Nullable

Default value

Field description

ProductReviewID

int

Primary key for ProductReview records.

ProductID

int

Product identification number. Foreign key to Product.ProductID.

ReviewerName

Name

Name of the reviewer.

ReviewDate

datetime

(getdate())

Date review was submitted.

EmailAddress

nvarchar
(50)

Reviewer's e-mail address.

Rating

int

Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ScrapReason](
[ScrapReasonID] [smallint] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.TransactionHistory

Record of each purchase order, sales order, or work order transaction year to date.

Fields

Field name

Data type

Nullable

Default value

Field description

TransactionID

int

Primary key for TransactionHistory records.

ProductID

int

Product identification number. Foreign key to Product.ProductID.

ReferenceOrderID

int

Purchase order, sales order, or work order identification number.

ReferenceOrderLineID

int

((0))

Line number associated with the purchase order, sales order, or work order.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[VendorAddress](
[VendorID] [int] NOT NULL,
[AddressID] [int] NOT NULL,
[AddressTypeID] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[VendorContact](
[VendorID] [int] NOT NULL,
[ContactID] [int] NOT NULL,
[ContactTypeID] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Sales.ContactCreditCard

Cross-reference table mapping customers in the Contact table to their credit card information in the CreditCard table.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesOrderHeaderSalesReason](
[SalesOrderID] [int] NOT NULL,
[SalesReasonID] [int] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

View: HumanResources.vJobCandidateEducation

View name:

HumanResources.vJobCandidateEducation

Description:

Displays the content from each education related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.

View: HumanResources.vJobCandidateEmployment

View name:

HumanResources.vJobCandidateEmployment

Description:

Displays the content from each employement history related element in the xml column Resume in the HumanResources.JobCandidate table. The content has been localized into French, Simplified Chinese and Thai. Some data may not display correctly unless supplemental language support is installed.

Stored procedure: dbo.uspLogError

Stored procedure name:

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.

Creation date:

26 Apr 2006

Last altered date:

26 Oct 2008

Parameters

Direction

Parameter name

Data type

Description

INOUT

@ErrorLogID

int

Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.

Stored procedure definition

-- 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;

Stored procedure: dbo.uspPrintError

Stored procedure name:

dbo.uspPrintError

Description:

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.