Discussion :

[pb_inge]

Bonsoir
J'aimerai recuperer les messages d'erreur
Qqu'un pourrait me mettre le code d'une procedure ?
Merci

P.S :

J'ai une procedure qui ne me renvoi pas toujours le bon message

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

as
set nocount on

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

[rudib]

Bonjour,

Merci d'expliquer plus clairement ce que tu veux faire.
En SQL Server 2005, tu as à disposition les blocs TRY ... CATCH avec des fonctions qui te retournent les informations sur les erreur, dont la fonction ERROR_MESSAGE() pour le message..