Error Message

Hi,
Is there any way to get the error message within a stored procedure. I
have to store the SQL Server error message in a table.
TIA
Thanks
PIn SQL Server 2005, you can use a TRY CATCH block similar to languages like
C# etc and use ERROR_MESSAGE() function for getting the error text.
In SQL Server 2000, you will have to use RAISEERROR.
"Prasad" wrote:
> Hi,
> Is there any way to get the error message within a stored procedure. I
> have to store the SQL Server error message in a table.
>
> TIA
> Thanks
> P
>
>|||Thnx Karthik
But I want something that is common to both SQL Server versions (2000
and 2005)
And AFIK the RAISEERROR generates a user defined message, I wanted the
error message that SQL Server generates.
Thanks
P
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:872116FB-5C9D-47BA-B322-0303A0BACEAF@.microsoft.com...
> In SQL Server 2005, you can use a TRY CATCH block similar to languages
> like
> C# etc and use ERROR_MESSAGE() function for getting the error text.
> In SQL Server 2000, you will have to use RAISEERROR.
> "Prasad" wrote:
>> Hi,
>> Is there any way to get the error message within a stored procedure.
>> I
>> have to store the SQL Server error message in a table.
>>
>> TIA
>> Thanks
>> P
>>|||You can't get to the error message at the TSQL level in 2000 or earlier. Only @.@.ERROR, which is the
error number.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:%23Zs4uZk0GHA.4796@.TK2MSFTNGP03.phx.gbl...
> Thnx Karthik
> But I want something that is common to both SQL Server versions (2000 and 2005)
> And AFIK the RAISEERROR generates a user defined message, I wanted the error message that SQL
> Server generates.
> Thanks
> P
> "Karthik" <Karthik@.discussions.microsoft.com> wrote in message
> news:872116FB-5C9D-47BA-B322-0303A0BACEAF@.microsoft.com...
>> In SQL Server 2005, you can use a TRY CATCH block similar to languages like
>> C# etc and use ERROR_MESSAGE() function for getting the error text.
>> In SQL Server 2000, you will have to use RAISEERROR.
>> "Prasad" wrote:
>> Hi,
>> Is there any way to get the error message within a stored procedure. I
>> have to store the SQL Server error message in a table.
>>
>> TIA
>> Thanks
>> P
>>
>|||Prasad (ekke_nikhil@.yahoo.co.uk) writes:
> But I want something that is common to both SQL Server versions (2000
> and 2005)
There isn't any. The best you can do on SQL 2000 is to use @.@.error to
retrieve the message text from sysmessages. But then you will only get
placeholders for parameterised strings. If you need to save the full
error message on SQL 2000, you can only do this from the client.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx