Solved

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

Posted on 2009-05-13
8
167 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
  • 4
  • 4
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
0
 

Author Comment

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

Expert Comment

by:Aneesh Retnakaran
Comment Utility
Some of them you can find on sysmessages table

select * from master..sysmessages  
0
 

Author Comment

by:adimit
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now