?
Solved

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

Posted on 2009-05-13
8
Medium Priority
?
175 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 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