Getting the last Error Message

I have a process that runs that pulls data from an Oracle database to SQL
server, when I encounter an error I'd like to write the error description
back to Oracle for the record that failed for reporting purposes. I can get
the code using @.@.ERROR but I'd like to have the message associated with this
error code populated with the field and table name and operation associated
with the error, example: Error Code 515, here's the associated message from
sysmessages: Cannot insert the value NULL into column '%.*ls', table '%.*ls'
;
column does not allow nulls. %ls fails.
I want the completed message that looks like this: Cannot insert the value
NULL into column 'vm2_location', table 'Intergy.dbo.vmailto'; column does no
t
allow nulls. UPDATE fails.
How can I get this...?
Thanks in advance..
DanIt's probably not what you are looking for, but DBCC OUTPUTBUFFER() will
contain the error message that is sent to the client. Unfortunately, I can't
think of any meaningful way of formatting it.
On the bright side, SQL Server 2005 has an ERROR_MESSAGE function which can
be used to capture the error message within a TRY..CATCH block.
"Alien2_51" wrote:

> I have a process that runs that pulls data from an Oracle database to SQL
> server, when I encounter an error I'd like to write the error description
> back to Oracle for the record that failed for reporting purposes. I can ge
t
> the code using @.@.ERROR but I'd like to have the message associated with th
is
> error code populated with the field and table name and operation associate
d
> with the error, example: Error Code 515, here's the associated message fro
m
> sysmessages: Cannot insert the value NULL into column '%.*ls', table '%.*l
s';
> column does not allow nulls. %ls fails.
> I want the completed message that looks like this: Cannot insert the value
> NULL into column 'vm2_location', table 'Intergy.dbo.vmailto'; column does
not
> allow nulls. UPDATE fails.
> How can I get this...?
> Thanks in advance..
> Dan|||The later sounds a lot nicer, nonetheless OUTPUTBUFFER is still an option,
thanks for the reply!!!
Dan
"Mark Williams" wrote:
> It's probably not what you are looking for, but DBCC OUTPUTBUFFER() will
> contain the error message that is sent to the client. Unfortunately, I can
't
> think of any meaningful way of formatting it.
> On the bright side, SQL Server 2005 has an ERROR_MESSAGE function which ca
n
> be used to capture the error message within a TRY..CATCH block.
> "Alien2_51" wrote:
>