ADO and RAISERROR

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
_Greg64Asked:
Who is Participating?
 
zdenek_hribConnect With a Mentor Commented:
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
 
namasi_navaretnamCommented:
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
 
_Greg64Author Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zdenek_hribCommented:
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
 
namasi_navaretnamCommented:
Good to know the solution. You close the issue then.

Regards,

Namasi
0
 
_Greg64Author Commented:
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
All Courses

From novice to tech pro — start learning today.