Solved

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

Posted on 2009-05-13
8
168 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
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 3 48
SQL Server Degrading on Write 13 67
Problem with SqlConnection 4 168
How to place a condition in a filter criteria in t-sql (#2)? 10 47
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 …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

770 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