?
Solved

ADO and RAISERROR

Posted on 2003-12-11
6
Medium Priority
?
714 Views
Last Modified: 2011-10-03
Hi,
I have application that connects to MS SQL 2000 using ADO. I’m trying to handle   multiple errors returned from store procedures. Everything works OK if I do not have cursor inside my store procedure. Once cursor is reached ADO driver fly out and I do not get rest of the error or record set. Just to be clearer here is the example of what happens:

In case of code like this:
RAISERROR 1
--other code
RAISERROR 2
-- other code
RAISERROR 3
Select statement

In my app I m able to get all 3 errors and record set

In case:
RAISERROR 1
--other code
RAISERROR 2
Open cursor
RAISERROR 3
Select statement

I’m getting only error 1 and 2 but not error 3 or record set after cursor.

Any suggestions are really appreciated
0
Comment
Question by:_Greg64
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9924891
Do you have any return statement between cursor statement and RaiseError 3?

RAISERROR 1
--other code
RAISERROR 2
Open cursor

-- Any retrurn statements here or any code that might cause sp to exit.

RAISERROR 3
Select statement

This works for me,

drop procedure sp_MyTest
go

create procedure sp_MyTest
AS
Begin

Select 1
RAISERROR('Test 1', 16, 1)

Select 2
RAISERROR('Test 2', 16, 1)

declare @i int

declare  c cursor for select 1 from publishers

open  c

Fetch next from c into @i

WHILE @@FETCH_STATUS = 0
BEGIN
  Fetch next from c into @i
END

CLOSE c
DEALLOCATE c

Select 3
RAISERROR('Test 3', 16, 1)

END
go

exec sp_MyTest
go
0
 

Author Comment

by:_Greg64
ID: 9928380
to namasi_navaretnam
Thanks for posting,
I probably should mention in the beginning. If I run my store procedure in query analyzer I’m OK too. I see all the error and record set. It is appear that problem is in ADO. You can simulate it if you try to debug you SP in query analyzer. SQL probably use ADO or OLE DB for debugging and you can see behavior that I have described in my question.

0
 

Expert Comment

by:zdenek_hrib
ID: 10128116
Hello,

  I have just encountered the same problem as you have. I am using all the recommended settings in ADO, like adExecuteNoRecords option, OLEDB provider for MS SQL Server and server cursor. I use the SET NOCOUNT ON command before I execute my stored procedure. After the FETCH NEXT FROM ... statement is executed, RAISERROR or PRINT commands results do not apear in ADOConnection.Errors collection anymore. It seems to be a bug in ADO. I have found only an article at http://www.algonet.se/~sommar/error-handling-II.html that is just describing the fact, that ADO is the most worse engine for error handling. I suppose you have not found a solution for you, did you ?
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Accepted Solution

by:
zdenek_hrib earned 750 total points
ID: 10128379
Hello again,

  after couple of experiments it seems that it is not a bug in ADO, but rather in OLEDB provider for MSSQL Server. I can get all the results of PRINTstatement in Errors collection if I have just switched the provider to OLEDB Provider for ODBC with ConnectionString set to Provider=MSDASQL.1;Password=xxx;Persist Security Info=True;User ID=sa;Extended Properties="Driver={SQL Server};Server=127.0.0.1;APP=MyApp;Network=DBMSSOCN";Initial Catalog=MyDB

(you may need to change the Network parameter in connection string if you use something else then plain TCP/IP to connect to SQL Server)
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 10129232
Good to know the solution. You close the issue then.

Regards,

Namasi
0
 

Author Comment

by:_Greg64
ID: 10130681
Hi zdenek_hrib,
Thanks for posting. I found this solution too, but once I switch to ODBC I’ve significant decrease in performance for my project. So we found another solution, decrease severity level of rise error to 10 or less. The problem now is that ADO do not throw exception any more, so even after successful call to SP you still need to check out error collection. Anyway I'm going to accept you answer.  

Regards,    
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 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