How to get past an error?

I have posted around this issue before, and am still looking for a soultion.

There are several situations where we have tsql code that could generate a
sql error but still need to return a recordset or print statement that
occours after the error.

I have been using the sqlclient class.

In the past (ado not ado.net) we could get errors followed by print
statemets and depending on the type of error, might see the recordset.. Now
with sqlclient the error blocks everything (even thought the code in the proc
completes with results.

It seems that the new envirionment is squashing the value of submitting code
in batchs.. As any error in a batch causes you to miss everthing else.

In QA I can see the errors, the print statements and the rows
(resultsets/recordsets/datatables - what ever you want to call them)

Does anyone know if this is going to be fixed (or are their any
workarounds).. Or do I need to programs all of my code to ODBC if I want to
see (and have my users) see all the results of their actions?

TIA

Rob
PS try to make this proc send back the error,print and get the record (row)
of databack if you are wondering what i am talking about.

Create proc someproc
as
declare @e int
delete from northwind.dbo.employees where employeeid = 2
--as far as ado.net is concerned, nothing happens after the above line :(
set @e = @@error
if @e <> 0
begin
print 'error number ' + cast (@e as varchar(300)) + ' Just happened'
end
select * from northwind.dbo.employees where employeeid = 1
.

Relevant Pages

  • Re: how to retrieve [RecordsAffected] count from cmd.Excecute in V
    ... the original j a variant was because Longs and Integers by default are 0. ... And here is the related ADO code to invoke the Proc and get the "Records ... assigning the result to a Recordset object) try removing the SELECT ...
    (microsoft.public.data.ado)
  • Re: Determining column names and types of recordset returned by stored procedure
    ... then wraps the call to the proc with SET NO_BROWSETABLE and SET FMTONLY ... the code above actually returns one empty recordset for each ... Visual Studio would only show TextResults as a resulting ... >> Studio 2003 Server explorer is reliably providing the information ...
    (microsoft.public.sqlserver.programming)
  • Re: Effects on RST in Calling Proc
    ... >> Obviously, the snapshot avoids that, but I think it's better to ... the recordset is snapshot and is passed to ... > the called proc so that another field can be searched for a value ... you avoid the issue entirely. ...
    (comp.databases.ms-access)
  • Re: Effects on RST in Calling Proc
    ... the original procedure in a way that is likely to survive the ... In the original proc, the recordset is snapshot and is passed to the called proc so that another field can be searched for a value that is being tested in the calling proc. ... I try as much as possible to avoid data manipulation by row processing and prefer set processing, ie, I prefer to execute an update or insert statement as opposed to going through each row in a recordset and doing ...
    (comp.databases.ms-access)