Solved

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

Posted on 2009-05-13
8
170 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Divide by zero error encountered. 2 44
How to use TOP 1 in a T-SQL sub-query? 14 48
INSERT DATE FROM STRING COLUMN 18 63
How come this XML node is not read? 3 26
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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