SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/**********************************************************************************************
Procedure Name   : GetErrorStringSP
Author           : Amit Jethva 
Date             : Mar 11 2004 5:30 PM
Purpose          : To get Error Message From SQL Server Error Log using sp
Tables Referred  : 
Input Parameters : 
   1. @in_IntErrorNumber  INT : The error number.
Output Parameters:
   1. @out_VcErrorString VARCHAR(1000) : The Error String from SQL Server Log 
      for current @@SPID and the passed Error number
**********************************************************************************************/

/*MODIFICATIONS HISTORY IN CHRONOLOGICAL ORDER, WITH THE LATEST CHANGE BEING AT THE TOP.*/ 
/******************************************************************************************** 

MODIFIED BY      : 
MODIFICATIONS    :
DATE             :

**********************************************************************************************/  

CREATE PROC  dbo.GetErrorStringSP 
   @in_IntErrorNumber INT ,
   @out_VcErrorString VARCHAR(1000) OUTPUT
AS
BEGIN

   CREATE TABLE #ErrorLog 
   ( 
      SrNo            INT IDENTITY(1,1) NOT NULL ,
      ErrorLogText    VARCHAR(255)      NOT NULL ,
      ContinuationRow INT               NOT NULL 
   )
   
   INSERT INTO #ErrorLog 
   EXEC master.dbo.xp_readerrorlog  


   DECLARE @VcLike1 VARCHAR(255) 
   DECLARE @VcLike2 VARCHAR(255) 
   DECLARE @IntFirstSrNo   INT
   DECLARE @IntLastSrNo    INT 

   SET @VcLike1 = '%spid' + CONVERT(VARCHAR, @@SPID)+ '%Error: %'   + CONVERT (VARCHAR, @in_IntErrorNumber )  + '%'
   SET @VcLike2 = '%spid' + CONVERT(VARCHAR, @@SPID)+ '%Error:%' 
   
   SELECT @IntFirstSrNo    = MAX (SrNo)  FROM #ErrorLog  WHERE ErrorLogText  like @VcLike1 OPTION ( KEEPFIXED PLAN ) 
   SELECT @IntLastSrNo     = MIN (SrNo)  FROM #ErrorLog  WHERE ErrorLogText  like @VcLike2 AND SrNo > @IntFirstSrNo  OPTION ( KEEPFIXED PLAN )   


   IF ISNULL(@IntFirstSrNo, 0 )     =  0 
   BEGIN
      SET @out_VcErrorString   = CONVERT (VARCHAR, @in_IntErrorNumber ) + ': NO DESCR AVAILABLE' 
      RETURN 
   END
   IF ISNULL( @IntLastSrNo , 0 ) =  0 
   BEGIN
      SET @VcLike2 = '%spid' + CONVERT(VARCHAR, @@SPID)+ '%' 
      SELECT @IntLastSrNo     = MAX (SrNo) + 1 FROM #ErrorLog  WHERE ErrorLogText  like @VcLike2 AND SrNo > @IntFirstSrNo    OPTION ( KEEPFIXED PLAN ) 
   END


   SELECT @out_VcErrorString   = ''

   SELECT  @out_VcErrorString   = @out_VcErrorString  + RTRIM( SUBSTRING(ErrorLogText , 34, LEN (ErrorLogText) - 33 ) ) + ' ' 
   FROM #ErrorLog 
   WHERE SrNo BETWEEN  @IntFirstSrNo AND @IntLastSrNo  - 1 OPTION ( KEEPFIXED PLAN ) 

   -- PRINT @out_VcErrorString     
   SET @out_VcErrorString   = CONVERT (VARCHAR, @in_IntErrorNumber )  + ': ' + @out_VcErrorString     

   
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO