Thanks Amit

Using your Idea I'm able to solve "Capturing The Error Description In A Stored..." for SQL 2000.

I would like to share my investigation with you guys.

Here are my findings:-

With this query I'm trying to create duplicate records in pubs.dbo.authors. By calling SP I get SQL error message.

USE [PUBS]

GO

INSERT INTO authors

values('172-32-1176','Doe','Jone','408-496-7223','Bigge Rd.','Menlo Park','CA','94345',1)

DECLARE @retval INT

EXEC usp_Get_ErrorMessage @retval output

Results:

[errNumber: 2627] [errState: 1] [errLevel: 14] [errInstance: MyLocal] [errLine: 1] [errProcedure: NULL] [errMessage: Violation of PRIMARY KEY constraint 'UPKCL_auidind'. Cannot insert duplicate key in object 'authors'.]

(1 row(s) affected)

Note:- Store procedure "usp_Get_ErrorMessage" is attached with this thread. rename .txt with .sql

Best of Luck

Logician --> [Tahir]