T-SQL Error handling

[Moptop]

Hi
Is it possible to access the SQL error string.

For instance:
If an INSERT fails due to a FK constraint, I get an error 547 stored in @@error.
I can look up that message using:
select * from master..sysmessages where error = 547
which retrieves:
%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

What I would like is to get the actual error massage:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'fkSegment_DivisionID'. The conflict occurred in database 'CustomerUK', table 'tblDivision', column 'vchrDivisionID'.
split up by the %ls placeholders.

I know about xp_sscanf but I don't know where to get error message from.

Does anybody have any ideas?

Thanks

Mark

 

[SQLSister]

sysmessages in Master database has the error messages. Is that what you are looking for?

 

[Moptop]

The error message in sysmessages has "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls."

What I need is the output from DBCC OUTPUTBUFFER (spid)

This contains the full error including table names, etc.

i.e. the stuff that goes into the %ls parameters

 

[Moptop]

I think I have an answer. I can insert the output of dbcc outputbuffer into a temp table.