Solved

Retrieving Error DEscription of an error in a stored procedure or other sql script

Posted on 2009-05-13
8
171 Views
Last Modified: 2012-05-06
This question is about error trapping without using try/catch.

I can goto an errorhandler when @@error > 0. What I don't know how to do is to retrieve the
error details, such as description, source etc.. when an error is generated. How do I do this?
0
Comment
Question by:adimit
[X]
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
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24379097
0
 

Author Comment

by:adimit
ID: 24379213
the goal as specified in my question is not to use try/catch.  
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24379390
Some of them you can find on sysmessages table

select * from master..sysmessages  
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:adimit
ID: 24379549
the sysmessages table does not provide a mechanism to determine which error is the one that I want. I only know the error number as a filter, and the sysmessages table does not provide a timedate stamp. Any other mechanisms?

The try/catch statements have too many limitations
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24379624
I dont think there is any other options ?

>The try/catch statements have too many limitations
Can u tell us what u r facing wrong with try..catch
0
 

Author Comment

by:adimit
ID: 24379702
at this point it seems that there is no way to do what I am asking for. I don't wan't to be difficult but the try/catch statement cannot be used in this case.  For one reason, I can't put in to stored procedures that are interacting with another piece of software that I have. Even if this were not the case the following excerpt from the sql server help should be enough:

A TRY&CATCH construct cannot span multiple batches. A TRY&CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY&CATCH construct cannot span two BEGIN&END blocks of Transact-SQL statements and cannot span an IF&ELSE construct.
at
Please let me know once again if there is something outside of the try/catch statement that I can use.

0
 

Author Comment

by:adimit
ID: 24379782
As an alternative, how can I send the error message and some custom text  to the sql log
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24379901
Did you check the RAISERROR WITH LOG  feature, whci can send the custom messages
http://msdn.microsoft.com/en-us/library/ms178592(SQL.90).aspx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

732 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