You CAN capture full error messages server-side (sp_altermessage)
I was talking to a colleague yesterday who was 100% CERTAIN that full error messages cannot be captured on the server at run time. He correctly pointed out that the SQL Profiler's Errors & Warnings: Exception event only reports the error number but lacks the error message detail (eg, which particular column name is missing from a table in a select statement). This fellow apparently hadn't heard of sp_altermessage's WITH_LOG feature, which writes full error log details to the server's Event Log. Even better, he was prepared to put a beer on it (c:
We happened to be discussing error 245 - which is a syntax level type conversion error, so I'll continue with this for my example. If such a 245 error occurs within SQL Server (2000), the Profiler displays the following output in its TextData column:
[Error: 245, Severity: 16, State: 1]
Sure, you get to see that the error happened & even what the number of the error condition was. You can even get the message template via the following query:
select * from master..sysmessage where error = 245
But this only yields the template & doesn't help you to drill into exactly what the error message detail contained:
Syntax error converting the %ls value '%.*ls' to a column of data type %ls.
To collect the actual error details, you simply configure the error message so that it logs the full message detail to the event log:
exec master.dbo.sp_altermessage 245, with_log, true
Then, you use SQL Profiler to capture Errors And Warnings: EventLog events (instead of Exceptions) with a filter on TextData like 'Error 245%'
You'll then get to see the full error message in the SQL Profiler, eg:
Error: 245, Severity: 16, State: 1
Syntax error converting the varchar value 'a' to a column of data type int.
That'll be a Heineken draught thanks (c: