Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ncaramello
Starting Member
1 Post |
 Posted - 2004-08-17 : 11:14:35
|
| I believe that there may be no solution to this problem, but....I want to be able to catch SQL Server Error Messages (note: not @@error, but the error that would be thrown to ADO, .NET, etc.) within a stored procedure, and write them to a table. Here's why: I have a staging table that I am using during a data import procedure. I have a cursor that reads from the staging table and writes to the transactional table. If the record can be written, it is deleted from the staging table. In the event that an error occurs the record stays in the staging table, and I want to be able to write the error to a column for that record in the staging table so that someone who comes along later to examine the error can see what the error was. @@Error is not a suitable thing to write since it does not contain any context for the error. The data import procedure needs to be self contained (i.e. I cannot throw the error to an .NET application and write it to the table) for performance and business reasons.Any ideas? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-17 : 13:31:16
|
| well since The Net-Libraries raise errors of their own, you can't do it the way you wan't to.from BOL:Error HandlingErrors raised in Microsoft® SQL Server™ 2000 have several attributes: Error number. Each error condition has a unique error number.Error message string. The error message gives diagnostic information about the cause of the error. Many error messages have substitution variables in which information, such as the name of the object generating the error, is placed. Every error number has a unique error message.Severity. The severity indicates how serious the error is. Errors with a low severity, such as 1 or 2, are information messages or low-level warnings. Errors with a high severity indicate problems that should be addressed as soon as possible.State code. Some error codes can be raised at multiple points in the source code for SQL Server. For example, an "1105" error can be raised for several different conditions. Each place the error code is raised assigns a unique state code. A Microsoft support engineer can use the state code from an error to find the location in the source code where that error code is being raised, which may provide additional ideas on how to diagnose the problem.Procedure name. If the error occurred in a stored procedure, the name of the stored procedure may be available.Line number. The line number indicates which statement in a stored procedure generated the error.All of the SQL Server errors are stored in the system table master.dbo.sysmessages. User-defined messages can also be stored in sysmessages. The RAISERROR statement can then be used to return these user-defined errors to an application if necessary.All the database APIs, such as ADO, OLE DB, ODBC, DB-Library, and Embedded SQL, report the basic error attributes: the error number and message string. However, there are variations in how many of the other error attributes each database can report.Other SQL Server components can also raise errors: The OLE DB provider, ODBC driver, and DB-Library dynamic-link library raise errors of their own. The format of these errors is consistent with the formats defined in the API specifications.The Net-Libraries raise errors of their own.Open Data Services raises errors in its own format.The SQL Server wizards, applications, and utilities such as the Index Tuning Wizard, SQL Server Enterprise Manager, and the osql utility can raise their own errors.Embedded SQL can raise SQL-92 errors.Go with the flow & have fun! Else fight the flow :) |
 |
|
|
|
|
|
|
|
Catching SQL Server Error Messages