Retrieve error desription for SQL Stored Procedure

srotondo · November 13th, 2009, 03:16 PM

is it possible to retrieve an error message from sql server stored prodedure and read it in my asp code?

for example, if i try to enter a dup key, can i pass the following stock SQL error message to asp from sql:

Server: Msg 547, Level 16, State 1, Procedure procReenrollmentContractHistoryAddUpdate, Line 50
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_reenrollmentContractHistory_reenrollmentContra ctTypeMaster'. The conflict occurred in database 'cmProd', table 'reenrollmentContractTypeMaster', column 'contractType'.
The statement has been terminated.

If i am executing one insert statement in my stored procedure and there's a problem, my asp WILL get this error message. However, i'm having a problem when I execute a select statement (which runs fine) and then execute an update statement which fails with a dup key. For some reason, my asp does not get the dup key message (probably b/c the select statement completed successfully).

See below. this is the code i'm having trouble with: (note that when i run it as is and force a dup key error, my asp shows no error. however when i comment out the exec procUpdateGetVariousNumbers2 statement, I DO receive a sql error in asp),

BEGIN TRANSACTION
set @logStatus = 'ADD'
exec procUpdateGetVariousNumbers2 'reenrollmentContractHistoryID', @logUser, @reenrollmentContractHistoryRecID output

insert reenrollmentContractHistory
select
@reenrollmentContractHistoryRecID,
@contractType,
@academicYear,
null, --paymentOption,
null, --tuitionRefundPlanYN,
null, --lunchPlanOption,
null, --tuitionAmount,
null, --tuitionDepositAmount,
null, --faAwardAmount,
null, --faScholarshipName,
null, --faScholarshipAmount,
null, --faDepositAmount,
null, --lunchAmount,
@contractText,
null, --@cshAdminUpdateName,
null, --@cshAdminUpdateDateTime,
null, --@cshAdminUpdateExplanation,
null, --@cshAdminApprovaleSignature,
null, --@cshAdminApprovaleSignatureDateTime,
parentID,
PrimaryAddressee,
PrimarySalutation,
NamePrefix,
FirstName,
MiddleName,
LastName,
NameSuffix,
reenrollmentStudentGrade,
reenrollmentStudentReenrollmentStatus,
reenrollmentStudentType,
constituentStatus,
@logUser,
@logDateTime,
@logStatus

srotondo · November 13th, 2009, 04:32 PM

Could my problem be due to a limitiation in ADO--it can only handle errors for one statement at a time?

If so, can i create the error description in the stored procedure and pass it to my ADO?

I know how to pass out the @@error number, but how do i get the description and pass it out in an output parm?

thank you!

Imar · November 13th, 2009, 04:41 PM

It's been a while, but I think you can use the Errors collection on the Connection: http://msdn.microsoft.com/en-us/libr...SQL.80%29.aspx

Cheers,

Imar

srotondo · November 13th, 2009, 04:57 PM

Imar,

thank you for your quick response.

I'm actually using what's specified in the link you sent me, however, it seems the ADO cannot handle multiple statements--it merely reports back on what happened in the first statement (error or success) and blows off the 2nd statement (see excerpt from Microsoft below--red bold).

what i want to do now is find a way do this: when i encounter an error in sql server stored procedure, save the error message text in a variable and pass it back to ado in an output parm. what do you think?

thank you for your help.

sal

=================

exerpt from Microsoft:

ADO applications use the Errors collection and the Error object to return provider-specific error information to an application. The Errors collection contains the errors generated by a single operation. Each Error object constitutes one such error in the collection. To get information about an error, query the properties of an Error object from the Connection object. To get all the Error objects in the Errors collection, use code to loop through the collection.

Imar · November 13th, 2009, 05:03 PM

Before you go that route, gave you tried

SET NOCOUNT ON

in your stored procedure? That "mutes" the output from the select statement and maybe allows the error message to bubble up...

Imar

srotondo · November 13th, 2009, 05:11 PM

yes. i did that but it didn't solve the problem.

thank you.

any thoughts on extracting the error message description from the stored procedure? is it possible?

sal

Imar · November 13th, 2009, 05:38 PM

It depends on the version of SQL Server you're using. Take a look under "client-side error handling" of this article:

http://www.sommarskog.se/error-handling-I.html

Imar

srotondo · November 13th, 2009, 05:51 PM

have a wonderful weekend.

sal

Imar · November 14th, 2009, 04:23 AM

Did it help? What are you using now to get access to the error messages?

Imar

srotondo · November 14th, 2009, 02:01 PM

Imar,

Thank you for checking in.

I've decided to keep my error processing in ASP. I've tested a few more things in SQL Server stored procedure and couldn't get them to work the way I wanted.

Ideally, I'd love to figure it out, but deadlines loom.

Thank you for all your help!

sal