What is error number 100001 used for? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What is error number 100001 used for?

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-03 : 18:11:42

Hello,

I am using SQL server 2000 and am fairly new. What does the error number 100001 mean? Is it for a user role or a group that receives an error message via e-mail?

Also, when creating temp tables, is it a good idea to create a clustered primary key at the time of creation or by an alter like so?

CREATE TABLE #New
(
securityId integer NOT NULL
, source integer NOT NULL
, dataDate integer NOT NULL
, currency integer NULL
, MarketCapMM float(53) NULL
, ImpliedMarketCapMM float(53) NULL
, hasOverride bit NOT NULL
, derivedPrimarilyFromSource integer NULL
, QCColumnFlags char(100) NULL
)
ALTER TABLE #New ADD PRIMARY KEY CLUSTERED( securityId, source )

Thanks in advance!
sqlnovice123

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-03 : 18:36:19

In BOL (Books Online), the error number 10001 is : "The provider reported an unexpected catastrophic failure."
So you will have to give more context in order for us to help you.

To create a primary key for a table is always a good idea.
It will ensure that duplicates are not entered in the table, and also indexes will be created for the key, so that performance will benefit.

rockmoose

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-03 : 18:52:44

Thanks rockmoose.

I believe it is more of a User Defined Function but I am not sure? The stored proc calls the LogError proc that has a parameter RoleID and a user defined variable called DefaultRoleID which is initialized to a value of 100001.

Thanks in advance!!!

Below is an extract of the stored proc:

DECLARE
@ErrorRoleID INTEGER

SELECT @ErrorRoleID = 100001 -- Global MIS
ProcError:
SELECT @ReturnError = CASE WHEN ISNULL(@ReturnCode, 0) <> 0 THEN 'Error #' + CONVERT(VARCHAR, @ReturnCode)
ELSE 'A user error'
END + ' has occurred while ' +
CASE WHEN ISNULL(@m, '') = '' THEN 'Running ' + @ProcName
ELSE @m
END
EXEC LogError
@DomainType = @DomainCode
, @ProcName = @ProcName
, @Message = @ReturnError
, @Debug = @Debug
, @RoleID = @ErrorRoleID

RAISERROR (@ReturnError, 1, 1)
RETURN -1

Here is the LogError stored proc:

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS OFF
go
IF OBJECT_ID('dbo.LogError') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.LogError
IF OBJECT_ID('dbo.LogError') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.LogError >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.LogError >>>'
END
go
/* Extra Credit: we can register proc to associate with a particular */
/* role. this way we can figure out dynamically who to */
/* send the mail message to depending on the subscription */
/* and association. */
/* (A Table maps ProcName with RoleID) */
/* Extra Credit 2: After implementing the ReturnStatus table. we can */
/* attach the error description to the mail message. */

/*******************************************************************************/
/* Procedure: LogError */
/* File name: Logerror.sql */
/* Version : 1.1 */
/* Date : March 5,1999 */
/* */
/* Description: This will log run time Error in the LogOutput table. By */
/* default LogError will sendemail to designated roles/users. */
/* */
/* The messageType will be assigned to 'E' - Error Message. */
/* For Information and Warning messages, use LogInfo and */
/* LogWarning. */
/* By default, the DetailLevel will be 3. */
/* */
/* NOTE: We will not check for any failures result from sending eamil or */
/* resoving RoleID to Email Address. This is because we are attempting*/
/* to log error, if any error occurrs we should not log that error */
/* also. */
/* */
/* EXAMPLE: */
/* */
/* SELECT @m = 'LogError: Ready to load 50 securities.' */
/* EXEC LogError 'FE', 'LoadFactsetData', @m */
/* , @RoleID = 100001, @Debug = @Debug */
/* */
/* Called by : All Stored Procs that require to log message in the */
/* database. */
/* Table Referenced : LogOutput */
/* */
/* Log Event numerical Codes */
/* 1 - Major Task */
/* 2 - sub task (start and end) */
/* 3 - error */
/* 4 - QC flag */
/* 5 - information */
/* */
/* Created Modified By: Lo Wang, March 15, 1999 */
/* */
/*******************************************************************************/

CREATE PROC LogError (
@DomainType VARCHAR(2)
, @ProcName VARCHAR(40)
, @Message VARCHAR(250)
, @DetailLevel INTEGER = 3
, @RoleID INTEGER = NULL
, @SendMail BIT = 1
, @SecurityID INTEGER = NULL
, @ExternalID VARCHAR(20) = NULL
, @ExternalIDType INTEGER = NULL
, @EntityID INTEGER = NULL
, @UseTestRecipients BIT = 0
, @Verbose SMALLINT = 1
, @Debug BIT = 0
)
AS
/****************************************************************************/
/* Parameter description: */
/* */
/* @DomainType -(VARCHAR(2)) Example: 'WF' for workflow. */
/* @ProcName -(VARCHAR(40)) The procedure that's logging the */
/* message. */
/* @Message -(VARCHAR(250)) Message to be logged. */
/* @DetailLevel -(INTEGER) default 3 */
/* @RoleID -(INTEGER) Email Recipient. (TBD) */
/* @SendMail -(CHAR(1)) Default Yes. */
/* @SecurityID -(INTEGER) */
/* @ExternalID -(VARCHAR(20)) */
/* @ExternalIDType -(INTEGER) */
/* @EntityID -(INTEGER) */
/* @UseTestRecipients-(BIT) If this is on then we will use the test */
/* recipient which is registerd in OSMValues global variable table. */
/* @Verbose -(SMALLINT) Debug Detail Level. */
/* @Debug -(BIT) Used to display Log message to the screen for */
/* the LogEvent. */
/****************************************************************************/

/****************************************************************************/
/* General Variables Declaration */
/****************************************************************************/

DECLARE @ErrorNum INTEGER /* keep track the global @@error */
, @ReturnStatus INTEGER /* actual return value (0 or 1) */
, @LoopID INTEGER

/****************************************************************************/
/* Application Specific Variables Declaration */
/****************************************************************************/

DECLARE @RecipientList VARCHAR(255)
, @RecipientListID INTEGER
, @Subject VARCHAR(70)
, @L1 VARCHAR(70)
, @L2 VARCHAR(70)
, @L3 VARCHAR(70)
, @L4 VARCHAR(70)
, @L5 VARCHAR(70)
, @DefaultRoleID INTEGER
, @Ordinal INTEGER
, @MailID INTEGER /* ID to identitfy worktable */
/* entry. */

/****************************************************************************/
/* Initialize Application Level Variables */
/****************************************************************************/

SELECT @DefaultRoleID = 100001 /* Global Research MIS */

/****************************************************************************/
/* Output to screen if debug is 1. */
/****************************************************************************/

IF (@Debug = 1)
BEGIN
SELECT 'ProcName = ' + @ProcName + ', MessageType = E, ' + @Message
END

/****************************************************************************/
/* Output to screen if debug is 1. */
/****************************************************************************/

IF @Message IS NULL
RETURN 0

IF (@SendMail = 1)
BEGIN

/************************************************************************/
/* Check if the RoleID is valid or not, if not set to default. */
/************************************************************************/

IF (@RoleID IS NULL) OR
NOT EXISTS ( SELECT 1 FROM WF_IBRoles
WHERE RoleID = @RoleID )
BEGIN
SELECT @RoleID = @DefaultRoleID
END

/************************************************************************/
/* Set up mail message. */
/************************************************************************/

SELECT @Subject = 'LogError: DetailLevel = ' + CONVERT(VARCHAR,@DetailLevel)
+ CASE WHEN @EntityID IS NOT NULL THEN ', EntityID = ' + CONVERT(VARCHAR,@EntityID) ELSE '' END
+ CASE WHEN @SecurityID IS NOT NULL THEN ', SecurityID = ' + CONVERT(VARCHAR,@SecurityID) ELSE '' END
+ CASE WHEN @ExternalID IS NOT NULL THEN ', ExternalID = ' + @ExternalID ELSE '' END
+ CASE WHEN @ExternalIDType IS NOT NULL THEN ', ExternalIDType = ' + CONVERT(VARCHAR,@ExternalIDType) ELSE '' END

SELECT @L1 = ' ' + ISNULL(SUBSTRING(@Message,1,67),'')
, @L2 = ' ' + ISNULL(SUBSTRING(@Message,68,67),'')
, @L3 = ' ' + ISNULL(SUBSTRING(@Message,135,67),'')
, @L4 = ' ' + ISNULL(SUBSTRING(@Message,202,50),'')
, @L5 = '---------------------------------------------------------------------'

/************************************************************************/
/* Post mail */
/************************************************************************/

EXEC @ReturnStatus = IB_PostMailMessageByRoleID @Source = 'LogError'
, @Sender = 'LogError'
, @RoleID = @RoleID
, @Subject = @Subject
, @Line2 = @L1
, @Line3 = @L2
, @Line4 = @L3
, @Line5 = @L4
, @Line10 = @L5
, @RemoveWorkEntry = 'N'
, @Verbose = @Verbose
, @Debug = @Debug

/************************************************************************/
/* Send Mail */
/* NOTE: We are already in LogError, if we have a problem with send */
/* send mail we will not try to log again. */
/************************************************************************/

EXEC @ReturnStatus = ML_SendMailMessage @Source = 'LogError'
, @UseTestRecipients = @UseTestRecipients
, @Verbose = @Verbose
, @Debug = @Debug

END /* end sendMail */

INSERT INTO LogOutput
(
EventTime
, DomainType
, ProcName
, MessageType
, DetailLevel
, Message
, SecurityID
, ExternalID
, ExternalIDType
, EntityID
)
VALUES
(
GetDate()
, @DomainType
, @ProcName
, 'E'
, @DetailLevel
, @Message
, @SecurityID
, @ExternalID
, @ExternalIDType
, @EntityID
)
go
IF OBJECT_ID('dbo.LogError') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.LogError >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.LogError >>>'
go
GRANT EXECUTE ON dbo.LogError TO oldPublicRole
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-03 : 19:34:22

>> SELECT @ErrorRoleID = 100001 -- Global MIS
>> EXEC LogError
>> ...
>> , @RoleID = @ErrorRoleID

10001 is some errorcode chosen by the programmer/system, and has nothing to do with sql server.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources