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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

zdenek_hribCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.