Solved

ADO and RAISERROR

Posted on 2003-12-11
6
712 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Accepted Solution

by:
zdenek_hrib earned 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

697 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