Retrieve an error message e.g. raiserror, constraint violation

if exists (select * from sysobjects where id = object_id(N'[dbo].[spFormatOutputBuffer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[spFormatOutputBuffer]
GO

create procedure spFormatOutputBuffer

@spid int,

@Buffer varchar(6000) output
as
/*
set nocount on
declare @s varchar(6000)
exec spFormatOutputBuffer @@spid, @s output
select @s
select @@spid

create table #b (id int identity (1,1), s varchar(100))
insert #b(s) exec ('dbcc outputbuffer(26)')
select * from #b
dbcc outputbuffer(26)

*/
declare @id int ,
@Chari int ,
@Hexi int ,
@HexOffset int ,
@charoffset int ,
@fill char(1) ,
@cmd varchar(100)

select @cmd = 'dbcc outputbuffer(' + convert(varchar(10),@spid) + ')'

create table #a (id int identity (1,1), s varchar(100))
insert #a(s)
exec (@cmd)

select @chari = 0 ,
@Hexi = 0 ,
@charoffset = 61 ,
@hexoffset = 12 ,
@fill = char(0)

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

select @Buffer = ''
select @Buffer = @Buffer + replace(substring(s, @charoffset, 16), @fill, '')
from #a

go

Read More