Error Reporting from Stored Procedures

How do you report errors from a stored procedure? For example, if the stored
procedure is trying to insert something into a table and the database is
full, how can the stored procedure return an error string to the caller?Hi Swami,
This article might help...
http://www.sqljunkies.com/Article/5...3B6D84BF94.scuk
"Swami" wrote:

> How do you report errors from a stored procedure? For example, if the stor
ed
> procedure is trying to insert something into a table and the database is
> full, how can the stored procedure return an error string to the caller?
>|||Thanks--great article.
"Ryan Randall" wrote:
> Hi Swami,
> This article might help...
> [url]http://www.sqljunkies.com/Article/564F5D77-2F7E-41FB-91C7-353B6D84BF94.scuk[/url
]
>
> "Swami" wrote:
>

Error recording

Hi all,

I have an application in production that use a lot of stored procedures and the application doesnt record any error if any during the operation. (There is only a RESUME NEXT sentence).

So I want to know if there is a way to make SQL Server to register an error if any during the execution of a Stored Procedure without changing any of them. Say, that I want SQL Server to register an error during an execution of an user defined Stored Procedure as an internal error.

Thanks in advance.

God Bless.Is there a problem trapping the error in the application (get rid of the resume next and handle the error) ?|||Originally posted by rnealejr
Is there a problem trapping the error in the application (get rid of the resume next and handle the error) ?

Yes, the application is in the production environment and we have to solve the problem as soon as posible. If we change the code in the application then after that we should test the app, and we dont have time.|||Off the top of my head - you could use alerts (but that would be tedious) - do you know what errors you want to trap ?

Error msg in OLD DB provider

I use OLE DB to call a series of stored procedures to add records to databas
e
tables within one transaction. I got the following errors:
Description: Cannot create new connection because in manual or distributed
transaction mode.
Source Description: Microsoft OLE DB Provider for SQL Server
The stored procedures are very simple. I do not try to create a new
connection explicitly. Neither do I try to use distributed transactions. The
only thing that might be a little complicate is I use ISequentialStream to
write blobs to table fields. The error msg confuses me. Any one has ideas
about this?Anothing might be interesting is that the error happens randomly and if I se
t
a break point in the code where it crashed and wait for a while before the
rest of stored procedures, you will not see the error and the save is
successful.
Roy
"Roy" wrote:

> I use OLE DB to call a series of stored procedures to add records to datab
ase
> tables within one transaction. I got the following errors:
> Description: Cannot create new connection because in manual or distributed
> transaction mode.
> Source Description: Microsoft OLE DB Provider for SQL Server
> The stored procedures are very simple. I do not try to create a new
> connection explicitly. Neither do I try to use distributed transactions. T
he
> only thing that might be a little complicate is I use ISequentialStream to
> write blobs to table fields. The error msg confuses me. Any one has ideas
> about this?|||The URL below mayhelp.
http://support.microsoft.com/defaul...&NoWebContent=1
Thomas
"Roy" wrote:

> I use OLE DB to call a series of stored procedures to add records to datab
ase
> tables within one transaction. I got the following errors:
> Description: Cannot create new connection because in manual or distributed
> transaction mode.
> Source Description: Microsoft OLE DB Provider for SQL Server
> The stored procedures are very simple. I do not try to create a new
> connection explicitly. Neither do I try to use distributed transactions. T
he
> only thing that might be a little complicate is I use ISequentialStream to
> write blobs to table fields. The error msg confuses me. Any one has ideas
> about this?