If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

The only place that I know to harvest these messages is in the TDS stream bound for the client. I don't think there is any way to trap them via stored procedures, logging (SQL or NT), or other means. If you find any answers (satisfactory or not), I'd like to hear about them!

You're probably retrieving only the first element of the error collection. You need to scroll through it either with FOR or with WHILE/DO...LOOP/etc. to retrieve all messages associated with the error.

Logging on the other hand can be easily enabled for this particular message by executing the following:

At least the way I read the original question was to see if the script/sproc could retrieve the text of the error message after substitution. I don't know how that can be done, since the text is passed into the TDS stream without signaling an event within the SQL engine (or setting an @@variable).

Using sp_altermessage would be a good first step, since it could trap the error message in the log file, but I still can't devine a way to get that back to the procedure that caused the error.

...the text is passed into the TDS stream without signaling an event within the SQL engine (or setting an @@variable)...
-PatP

The @@error global memory variable contains the last error that occurred, whether it's logged or not. I just ran a test on 2601 and the following statement returned what I expected:

--Attempt to insert a duplicate value while UNIQUE indes exists on the field...
set @ErrorNumberFromGlobalErrorVariable = @@error
select * from master.dbo.sysmessages where error = @ErrorNumberFromGlobalErrorVariable

"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."

I think you do, because by the time "set @ErrorNumberFromGlobalErrorVariable=@@error" completes, the error is already logged to the errorlog with fully expanded string substitution for every instance of the place holder. The client is about to be presented with the error in the same format.

"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."