Solved

ADO and RAISERROR

Posted on 2003-12-11
6
703 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now