Problems with Errors (Is this a bug)

[Guest]

Scenero:
Run a batch file..
Multiple Querys...
An error occours in 1 query and in QA all other querys will run and return
results.
In dot net I can't get past the error..
ie
Select top 1 lastname from northwind.dbo.employees
raiserror ('Error Here',14,2)
Select top 1 firstname from northwind.dbo.employees

In QA I would see Davilo - Error here - and Nancy
2 recordsets / datatables and an error...

In ADO.Net the best I get is the first Recordset(datatable - set of records
- whatever you want to think of it as.....)

If I use a datareader I end up getting a wonderfull error of
"A severe error occurred on the current command. The results, if any,
should be discarded."
As soon as I try to access the second recordset/set of records/group of
readonly rows.
It won't let me see the second recordset, it just dies at that point...

Any clues as to how to get a valid result like QA are welcome.!

TIA

Rob

PS this seems kind of buggy to me... If I shouldn't be able to get the
results in DotNet, why does the SQL Server tool let me?

 

[Marina]

ADO.NET is not the equivalent of query analyzer, don't expect it to act as
such. They are very different, and you can't do everything with ado.net that
you can in QA.

Like you said, QA is a tool. So it is written to be able to do certain
things.

 

[Guest]

I agree (QA is a tool), but what QA shows is that something happens after the
error...

If I don't know that because ADO.NEt doesn't show it, it is a BUG in ado..
It is keeping information to itself and not letting the client know! That
shouldn't be up to ADO to filter this improtant infromtaion out!

How can this be fixed or can we be certain we are being given the correct
result of a transaction...>?????????


Rob

 

[Marina]

I don't think you understood my post.

What QA can do is no relation to what ADO.NET can do. So you can't expect
the 2 to do the same thing. So just because ADO.NET does not have the same
capabilities or a different model of how it executes statements, does not
mean it is a bug!!!

ADO.NET does let you know that the error has occurred, so I don't see the
problem. If you don't like the fact that you can not get the results of the
second query, execute them separately.

Additionally, ADO.NET should not be used for processing scripts. That is not
what it is there for. If you need to be able to run arbitrary sql scripts
that have GO statements, etc, you need to use SQL DMO.

 

[Guest]

I understand where you are comming from. But what if I wanted to write
something like QA. The MS tools don't let me do this without having to go
back to the deep dark ages of ODBC or maybe using SQLDMO! that is a giant
step backwards.

Also YOu obviously don't undestand all the issues.. WHat if you were calling
a proc and one step failed, but then the next step did 3 deletes and 2
inserts but the error of the first one prevents you from seeing this/..

BUG

 

[Guest]

Sorry really didn't mean to thankyou for you post.

SQL DMO isn't guaranteed to stay the same (look at sql 2005) and then you
get com reg errors> It thought DOT NET was supposed to get rid of these
issues not ADD to them!!!

MS - fix this ! This is tooo limiting!!

MSCE, MCDBA 7 and2k (pluss 6.5 certs) & MCSD vb6 etc..\

 

[Guest]

create database crap
go
use crap
go
create table abc (c1 int)
go
select top 1 * from northwind..employees
raiserror ('How bad is this',12,3)
insert into abc values(1)
select * from abc
go
drop table abc
go
drop database crap

If you run this script you will see that you added a row! but you would
never see this with ADO.Net

How can you trust it as a tool if it doesn't show you all that is happening!

Rob

 

[WJ]

"rob lynch"

create database crap
go
use crap
go
create table abc (c1 int)
go
select top 1 * from northwind..employees
raiserror ('How bad is this',12,3)
insert into abc values(1)
select * from abc
go
drop table abc
go
drop database crap

If you run this script you will see that you added a row! but you would
never see this with ADO.Net

How can you trust it as a tool if it doesn't show you all that is
happening!

What you are attempting is not impossible. One such clean method is to
execute the above script inside SQL/Stored Procedure. In SQL/SPROC, you then
can interupt each step to examine the execution result and return them to
your application appropriately once the entire transaction is completed
regardless of execution status on each step.

Example: In your SP, you may want to do this:

insert into abc values(1)

--Insert your validation code here
IF @@ROWCOUNT>=1
BEGIN
SET @OUTPUTKOUNT=@@ROWCOUNT
END
ELSE
BEGIN
SET @OUTPUTKOUNT=-1
END
.....fall thru here to continue other steps...

--finally do:
RETURN cotrol back to your app..

Once control returned to your .Net application, you will then examine the
@OUTPUTKOUNT and show the result message to whatever you want. An SP may
return more than one output results. You just need to specify them in a
parameter collection and define them as "Output".

PS: The samples of this type of application are plentiful online.

John

 

[Guest]

WJ thanks for the lesson in error handling.
I am not worried about my ability to write good sql.. It is everyone else’s
ability to write bad code that concerns me.

If I can't accurately display the results to the client (no matter what
errors show up) the entire integrity of the clientapp is suspect. And if I
(and you for that matter) can't know for sure that an adhoc statement did or
didn't work 100% then ......

It doesn’t make sense that a command can return a couple of rows, generate
an error, delete some other rows, update a couple more and drop a table,
without letting me know that I just deleted a few rows and removed a table
from my database.

Yes this isn’t something you need to worry about in most apps but what
happens if someone “does manage to hack into your server†and change some
stored procs that will do something malicious but never let you know…. (and
of course that has never happened to anyone and even if it did it couldn’t
happen to you)

THIS IS A DANGEROUS ISSUE. Everyone needs to be concerned!

Rob

PS
You code wouldn't work
@OUTPUTKOUNT would never be correct unless no records were returned.. Global
variables only last as long as the last statement and as soon as you check
it, it has changed.

Run the following code.
declare @r int
select * from master..sysdatabases
if @@rowcount > 0
begin
print cast( @@rowcount as varchar(3000)) + ' is the rowcount'
end

You will find the rowcount is always 0

 

[Stephany Young]

You are absolutely correct Rob, it is a serious issue. But, it is a SQL
Server issue. It is not an ODBC issue - it is not an ADODB issue and is not
an ADO.NET issue.

 

[WJ]

"rob lynch"

...but what
happens if someone "does manage to hack into your server" and change some
stored procs that will do something malicious but never let you know..
(and
of course that has never happened to anyone and even if it did it couldn't
happen to you)

From this earth, as I have seen so far, no one (including Bill Gate) is
able to design/offer an absolute safe system or product for you! All one can
say is do the best s/he can to secure the system. The tools provide you
with enough "get arounds" such as system log, trace.... Log your
transaction, and monitoring your activity daily.... earn a few $, go home
feed yourself, back the next morning, see your log again. If nothing
happened last night then cashflow still active to-from your pocket.
Otherwise, ....;...;..

THIS IS A DANGEROUS ISSUE. Everyone needs to be concerned!

Yes. It is a violent world isn't it ! You just hook on your wireless box and
a minute later your box is full of bs...

John