Recording Error Messages - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Recording Error Messages

Author

Topic

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-15 : 09:26:00

Hi,

Does anyone know how I could record error messages inside of a stored proc.

Take this for example:

--***************************************************************
CREATE TABLE TestParent1(IDField INT IDENTITY(1, 1), Descr VARCHAR(100))
CREATE TABLE TestParent2(IDField INT IDENTITY(1, 1), Descr VARCHAR(100))
CREATE TABLE TestChild1(Parent1 INT, Parent2 INT)

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.TestParent2 ADD CONSTRAINT
PK_TestParent2 PRIMARY KEY CLUSTERED
(
IDField
) ON [PRIMARY]

GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.TestParent1 ADD CONSTRAINT
PK_TestParent1 PRIMARY KEY CLUSTERED
(
IDField
) ON [PRIMARY]

GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.TestChild1 ADD CONSTRAINT
FK_TestChild1_TestParent1 FOREIGN KEY
(
Parent1
) REFERENCES dbo.TestParent1
(
IDField
)
GO
ALTER TABLE dbo.TestChild1 ADD CONSTRAINT
FK_TestChild1_TestParent2 FOREIGN KEY
(
Parent2
) REFERENCES dbo.TestParent2
(
IDField
)
GO
COMMIT
--*******************************************************************

Now run the following command in QA:

Insert INTO TestChild1 VALUES(10, 12)

The following error is returned because of a foreign key constraint violation:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_TestChild1_TestParent1'. The conflict occurred in database 'Workdb', table 'TestParent1', column 'IDField'.
The statement has been terminated.

NOW If I want to write this error to a error table if it was in a stored proc, I could do this:

select * from master.dbo.sysmessages where error = @@Error

The error description in the result set is this:
%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

-- This is what the error in QA reads, But it has wild cards in it(%ls)

How on earth would I record these messages with the relevant values filled in, in the wildcard placeholders?

I don't want to do it using ado - I want to do it from within SQL.

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 10:19:09

Don't know about catching the text of the message, but:


DECLARE @intErrNo int
Insert INTO TestChild1 VALUES(10, 12) 
SELECT @intErrNo = @@ERROR
IF @intErrNo <> 0 SELECT 'Error Captured Successfully:' + CONVERT(varchar(20), @intErrNo)

will "capture" the error (unless you are using SET XACT_ABORT OFF in which case the thing that called the SProc will have to check @@ERROR)

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-15 : 10:33:24

700 Already Kristen- You hit 600 just 2 days ago - I think we need to enforce some kind of handicap on you.

As for your post
No - That I know I can do already.
Thing is some guys here at our place have written massive sprocs and record these messages(from sysmessages) in errorlog type tables and they view the error log each morning.

I personally would have done this with some sort of layer using ado to handle this - as you can pick up the message from ado.

But the error message from sysmessages without the placeholders filled in is meaningless.

These guys don't want to re-write the code using this ado type approach.

any other ideas?

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 11:19:48

> any other ideas?

Production then is it? So we can only be talking one or two errors a week, tops. So knowing that an error has occurred and putting some appropriate parameters into a EXEC in QA to cause the error to reproduce is not a big issue is it?

If > 1~2 errors / week then HTF did it get to production? <g>

FWIW (no help, 'coz they don't want to rewrite them. which I can understand) we do:


CREATE PROCEDURE dbo.MyProcName
 @strParam1 varchar(nn),
 @intParam2 int,
 ...
AS
SET NOCOUNT ...
DECLARE @strParams varchar(8000),
        @intLogID  int,   -- ID of entry in LOG table
        @intTemp   int,
        @intErrNo  int,   -- Local variables for error handler
        @strErrMsg varchar(8000),
        etc.
SELECT  @strErrMsg = ''   -- Initialise
...
-- Concatenate parameters
SELECT @strParams = COALESCE(@strParam1, 'NULL')
               + '~' + COALESCE(CONVERT(varchar(20), @intParam2, 'NULL')
               ...
-- Store the parameters in LOG table with a 'Fake' error number
EXEC @intLogID = dbo.MyLogSProc(@name='MyProcName', @params=@strParams, @ErrNo = -999)
...
Insert INTO TestChild1 VALUES(10, 12) 
SELECT @intTemp = @@ERROR
IF @intTemp <> 0
BEGIN
    -- Error Handler
    SELECT @intErrNo = -1, 
           @strErrMsg = @strErrMsg 
                      + 'Insert TestChild1 failed [' 
                      + CONVERT(varchar(20), @intTemp) + '].  '
--  Optional GOTO MyProcName_EXIT

... more stuff ...

MyProcName_EXIT:
-- Freshen Log with and REAL error number (or 0=No error)
EXEC dbo.MyLogUpdateSProc(@logID=@intLogID, @ErrNo=@intErrNo, @ErrMsg=@strErrMsg)
RETURN @intErrNo  -- Return our "internal" error code to caller

We have a report which prints out anything in the LOG which does NOT have and ErrNo of 0. "-999" means that the SProc started and didn't finish (Sytnax error/deadlock etc.), other ErrNo can be found by looking for that, unique, number within the source code.

The LOG records the parameters passed to the SProc, so we can just run the SProc in QA with the appropriate parameters to see what happens. We don't need the actual SQL error message, although the Error Number can be handy. (I suppose the real message might be useful on occassions, but I can't think of when we weren't able to reproduce an error with this method).

We do Web Site stuff so a) we can't trot round and look over a user's shoulder and b) we also have a SESSION number that we store int eh log, which ties together all log entries for a given user's session (and if the Log In then we can attribute it to a real user - e.g. if they DO fill in a "I had problems" web form.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-15 : 12:27:35

I echo out messages to a file and then as I leave to sproc I bcp them in to a log...just before the ROLLBACK in case of sproc failure...

2 things...the first echo is '>', and the filename might be made to be unique...say like with a timestamp....this process is isoltated...


SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))
	+'|Starting Quarterly Load Process'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' > d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

-- sproc code

SET @var = RTrim(Convert(Char(20),@Log_Id)) + '|U|'+RTrim(Convert(char(50),GetDate(),109))
	+'|Load Completed Successfully'
SET @cmd = 'echo ' + '"|' + @var + '|"' + ' >> d:\Data\Tax\log_out.txt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'

Exec(@Command_String)

SET @cmd = 'bcp ' + @db_name + '..Tax_Load_Log in d:\Data\Tax\log_out.txt -t"|" -c -S' 
	+ @@servername + ' -U -P'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''''
Select @Command_string 

Exec(@Command_String)

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-15 : 12:39:59

Is there a big performance penalty in using xp_cmdshell ECHO and BCP over, say, INSERT into table?

I suppose the BCP only needs to happen once in a while ... everything can just append to "d:\Data\Tax\log_out.txt" and then periodically something can come along and RENAME it [to prevent concurrent appends] and BCP it in.

The ROLLBACK thingie is a RIGHT PAIN though ... I lose all my logging context when an outer SProc does a ROLLBACK.

If I INSERT into a LOG table in another databases, on same server [without any distributed transaction etc.] I suppose that will that get rolled back too?

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-19 : 01:40:13

HTF did it get into production?
That's what I said!! - these people work on different systems to me, I'm just doing them a favour by trying to find the solution.

I have now decided to let them figure it out for themselves.

Thanks for the help anyways.

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 04:56:08

"I have now decided to let them figure it out for themselves."

Hehehehehe! Very Smart Move Mate!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-07-19 : 08:59:55

Capture error in proc
RAISERROR(.....) WITH LOG
Then look in the Application Log

/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources