CREATE procedure [dbo].[SP_ERR__FORMAT_OUTPUTBUFFER] @spid int, @Buffer varchar(6000) output 

as 


declare @id int ,
@chari int , 
@hexi int , 
@hexOffset int , 
@charoffset int , 
@fill char(1) , 
@fill2 char(1) , 
@cmd varchar(100), 
@hexdata varchar(6000), 
@hexdata_tmp varchar(80),
@buffer_tmp varchar(80),
@nl varchar(2), 
@w_id int,
@hexndx int, 
@finalndx int, 
@charndx int, 
@msg numeric(12), 
@level numeric(12), 
@state numeric(12), 
@linenumber numeric(12), 
@tempstr varchar(20),
@charlen int,
@t varchar(6000) 

set nocount on
--set @Buffer = ''
-- setup the command to get the last output buffer for the given spid 
select @cmd = 'dbcc outputbuffer(' + convert(varchar(10),@spid) + ')' 

-- dbcc output buffer returns multiple lines, each at most 80 characters long 
create table #a (id_a int identity (1,1), s varchar(80)) 

insert #a(s) exec (@cmd) 

--select * from #a


if @@ERROR = 2571
begin 
select 'erreur 2571'
-- Error 2571 indicates that the current user does not have permission to run dbcc outputbuffer 

set @Buffer = 'NO OUTPUT INFORMATION IS AVAILABLE. THE CURRENT USER DOES NOT HAVE PERMISSION TO RUN DBCC.' 
return 
end 

select @chari = 0, @hexi = 0, @charoffset = 62, @hexOffset = 12, @fill = char(170), @fill2 = char(171), @nl = char(10) --+ char(13) 

-- Data Format -- --00000000 04 01 01 4e 00 45 0c 00 00 30 00 20 00 32 00 65 ...N.E...0. .2.e 
--_Byte #__ ___________ASCII HEX Vals for Chars________ _ASCII Chars_ -- 
-- Byte # This is the number of the starting byte in the line. 
-- ASCII HEX Vals These are ascii representations of the 
-- hexidecimal values of each char. Each set of 
-- two numbers represents a character. 
-- ASCII Chars These are the actual ascii characters in the 
-- buffer. -- 
-- This procedure looks up specific characters such as string lengths from the 
-- ascii hex section and actual characters from the ascii char section. 

-- set period characters in the ascii char section to char(170) so than can be 
-- easily stripped out later. Check for 0x2e in the ascii hex section and save 
-- those periods as needed. 

while @chari < 16 
begin 
update #a set s = stuff(s, @charoffset+@chari, 1, @fill) 
where substring(s, @charoffset+@chari, 1) = '.' and 
substring(s, @hexOffset+@hexi, 2) <> '2e' 
select @chari = @chari + 1 , @hexi = @hexi + 3 

end 

set @w_id = coalesce((select min(id_a) from #a where charindex('00 aa',s)>0 ),0)
delete from #a where id_a < @w_id-2

-- compile all of the ascii hex section into one long string 
select @hexdata = '' 
select @hexdata_tmp=''
declare hexdata_cursor cursor for
select substring(s, @hexOffset, 48) from #a ;

open hexdata_cursor;

FETCH NEXT FROM hexdata_cursor
into 
@hexdata_tmp;


WHILE @@FETCH_STATUS = 0
BEGIN

set @hexdata = @hexdata + @hexdata_tmp;

FETCH NEXT FROM hexdata_cursor
into 
@hexdata_tmp;

END
CLOSE hexdata_cursor;
DEALLOCATE hexdata_cursor;


--select @hexdata = @hexdata + substring(s, @hexOffset, 48) from #a --where #a.id_a >= @w_id 

--set @hexdata = @hexdata +''
--select 'coucou'
--select substring(s, @hexOffset, 48) from #a where #a.id_a >= @w_id 

-- fe 03 is the marker which signals the end of the useful data in the 
-- ascii hex section. Search for this point to mark the end. While searching 
-- for the end also find the beginning of the useful data ( an fd in the ascii hex section). 

set @finalndx = 1 
--while substring(@hexdata, @finalndx, 5) <> 'fe 03' and @finalndx < 6000 
while substring(@hexdata, @finalndx, 5) <> '00 aa' and @finalndx < 6000
begin 
set @finalndx = @finalndx + 3 
if substring(@hexdata, @finalndx, 2) = 'ff' 
begin
set @hexndx = @finalndx 
--select @hexndx 
end
end 

while substring(@hexdata, @finalndx, 5) <> 'ff 13' and @finalndx < 6000
begin 
set @finalndx = @finalndx + 3 
end 

--select '@hexdata = ' + @hexdata 

-- capture the error msg number and convert it from ascii 
set @hexndx = @hexndx + 36 

set @tempstr = substring(@hexdata, @hexndx, 6) 


exec SP_ERR__HEX_TO_NUMERIC @tempstr, @Number=@msg OUTPUT 



-- capture the error state number and convert it from ascii 

set @hexndx = @hexndx + 12 
set @tempstr = substring(@hexdata, @hexndx, 3) 
exec SP_ERR__HEX_TO_NUMERIC @tempstr, @Number=@state OUTPUT 

-- capture the error level number and convert it from ascii 

set @hexndx = @hexndx + 3 
set @tempstr = substring(@hexdata, @hexndx, 3) 
exec SP_ERR__HEX_TO_NUMERIC @tempstr, @Number=@level OUTPUT 


-- capture the character length of the first block of characters in the message and 
-- convert it from ascii 
set @hexndx = @hexndx + 3 
set @tempstr = substring(@hexdata, @hexndx, 6) 
exec SP_ERR__HEX_TO_NUMERIC @tempstr, @Number=@charlen OUTPUT 


-- capture the error line number and covert it from ascii 

set @tempstr = substring(@hexdata, @finalndx-6, 6) 

exec SP_ERR__HEX_TO_NUMERIC @tempstr, @Number=@linenumber OUTPUT 

--select @msg
/*
select @Buffer = '' 
select @Buffer = @Buffer + substring(s, @charoffset, 16) from #a 
*/

select @Buffer = ''
select @buffer_tmp=''
declare buffer_cursor cursor for
select substring(s, @charoffset, 16)from #a ;

open buffer_cursor;

FETCH NEXT FROM buffer_cursor
into 
@buffer_tmp;


WHILE @@FETCH_STATUS = 0
BEGIN

set @Buffer = @Buffer + @buffer_tmp;

FETCH NEXT FROM buffer_cursor
into 
@buffer_tmp;

END
CLOSE buffer_cursor;
DEALLOCATE buffer_cursor;


--print 'Buffer ' + @Buffer
--print 'hexdata '+@hexdata
--set @hexndx = @hexndx + 6 
set @hexndx = @hexndx + 6
set @charndx = @hexndx 

-- find the next break between character blocks, save the number of characters in the 
-- next block, and replace the char count with char(171), repeat for each char block 

--while @charndx < (@finalndx - 6) and @charndx < 6000


while @charndx < (@finalndx - 6) and @charndx < 6000
begin 
set @charndx = @charndx + (@charlen *6) 
set @tempstr = substring(@hexdata, @charndx, 3) 

exec SP_ERR__HEX_TO_NUMERIC @tempstr, @Number=@charlen OUTPUT 
set @Buffer = stuff(@Buffer, (@charndx/3+@charndx%3),1, @fill2) 
set @charndx = @charndx + 3 
end 
select @Buffer = substring(@Buffer, (@hexndx/3+@hexndx%3), ((@finalndx/3+@finalndx%3)-(@hexndx/3+@hexndx%3))) 

-- eliminate the char(170) values 

select @Buffer = replace(@Buffer, @fill, '') 

-- replace the other breaks between strings with which were changed to char(171) above with @nl which is a line feed 

select @Buffer = replace(@Buffer, @fill2, @nl) 

-- concatenate the msg, level, state, and line numbers to the front of the string being returned 

set @Buffer = 'Server: Msg ' + convert(varchar(10), @msg) + ', Level ' + convert(varchar(10), @level) + ', State ' + convert(varchar(10), @state) + ', Line ' + convert(varchar(10), @linenumber) + @nl + @Buffer 

drop table #a