SQL Server 2000 ErrorMessages vs SQL Server 2005

Posted on 2007-11-25
Last Modified: 2010-03-19
In sqlserver 2000  I check @@Error after each and very SQL Statement and if > 0 return -1

In Current 2005 in Catch Block  I use ERROR_MESSAGE (System function) to catch error and Raise it back to my client.

In SQL Server 2000  Cant we use ERRORMESSAGE (Detailed Description of Error like in SQLServer 2005) in a stored procedure...
Question by:dotnet0824
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 15

Assisted Solution

spprivate earned 50 total points
ID: 20345753
This is a goodway to handle errors with detailed messages
LVL 31

Accepted Solution

James Murrell earned 150 total points
ID: 20346290

Author Comment

ID: 20346708
so in sql server 2000 we have to create our own custom message descriptions to give a clear picture of the Error by adding to to sp_addmessage

But in SQL Server 2005 we dont need to do anything of that sort and its pretty straight forward using
inbuilt system function ERROR_Message ......Am I right?
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 31

Expert Comment

by:James Murrell
ID: 20346845
as far as i am aware yes...
LVL 18

Assisted Solution

Yveau earned 50 total points
ID: 20348876
Yep, in the catch block just use the ERROR_MESSAGE() function to retrieve the error that caused that fact that the catch block was entered. So no more @@Error catching after each SQL statement, just run some code in a try block and do the error tracking and handling in the catch block with the above mentioned function.

Also these functions can add some nice functionality:
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
(also in the catch block !)

Hope this helps ...

Author Comment

ID: 20351990
So my final quesiton is this... IN Sql Server 2000 There is no way to catch and use RaisERROR to the client directly like u do in SQL Server 2005 ERROR_Message............. So only ErrorNumbers can be Raised in SQL Server 2000  ........So no message information being raised
LVL 31

Expert Comment

by:James Murrell
ID: 20352076
I think you are correct with that assumption
LVL 18

Expert Comment

ID: 20352219
Indeed, that is correct ... when you do not declare the user messages first.

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question