Transact SQL Error Messages

[Richard Berman]

After spending a couple house with the SQL Server Books On-Line, I don't appear
to be any closer to answering this question. In a stored procedure, I can
easily see an error number with @@Error. I can use it to look up the template
for the error message.

What I can't do is get THE error message. For example, if I do an insert
that violates a check constraint, I get @@error 547. What I can't get is
the actual error message, with template values filled in, so that I can see
exactly what was violated!

Obviously it exists, as pretty much all the object wrappers for SQL Server
return error messages (not templates). But how can I find the error message
corresponding to the exact thing that generated the error, in T-SQL?

Thanks!

[DaveSatz]

The closest I can come to this with TSQL is:

select * from master..sysmessages
where error = 547

but you would get: %ls statement conflicted with %ls %ls constraint '%.*ls'.
The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.

--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

"Richard Berman" <rb@sts1.com> wrote in message
news:3aa2e986$1@news.devx.com...
>
> After spending a couple house with the SQL Server Books On-Line, I don't
appear
> to be any closer to answering this question. In a stored procedure, I can
> easily see an error number with @@Error. I can use it to look up the
template
> for the error message.
>
> What I can't do is get THE error message. For example, if I do an insert
> that violates a check constraint, I get @@error 547. What I can't get is
> the actual error message, with template values filled in, so that I can
see
> exactly what was violated!
>
> Obviously it exists, as pretty much all the object wrappers for SQL Server
> return error messages (not templates). But how can I find the error
message
> corresponding to the exact thing that generated the error, in T-SQL?
>
> Thanks!
>

[Kevin Patrick]

If you run the statement through query analyzer it will return the proper
error description to you.

KP

"DaveSatz" <davidsatz@yahoo.com> wrote:
>The closest I can come to this with TSQL is:
>
>select * from master..sysmessages
>where error = 547
>
>but you would get: %ls statement conflicted with %ls %ls constraint '%.*ls'.
>The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls.
>
>--
>HTH,
>David Satz
>Principal Software Engineer
>Hyperion Solutions
>->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>
>"Richard Berman" <rb@sts1.com> wrote in message
>news:3aa2e986$1@news.devx.com...
>>
>> After spending a couple house with the SQL Server Books On-Line, I don't
>appear
>> to be any closer to answering this question. In a stored procedure, I
can
>> easily see an error number with @@Error. I can use it to look up the
>template
>> for the error message.
>>
>> What I can't do is get THE error message. For example, if I do an insert
>> that violates a check constraint, I get @@error 547. What I can't get
is
>> the actual error message, with template values filled in, so that I can
>see
>> exactly what was violated!
>>
>> Obviously it exists, as pretty much all the object wrappers for SQL Server
>> return error messages (not templates). But how can I find the error
>message
>> corresponding to the exact thing that generated the error, in T-SQL?
>>
>> Thanks!
>>
>
>