SQLDataProv inconsistencies returning multiple RAISERRORS to SQLEr

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK...

Can't find alot about this but by reading .NET sqlError and
sqlErrorCollection I would hope I would get the same behavior issuing calls
to SQL Server 2000 sprocs. The problem...unless you use
sqlCommand.ExecuteNonQuery() you will only receive one sqlError (i.e. use the
sqlDataAdapter.Fill() to reproduce) no matter how many times you issue a
RAISEERROR (severity 11 in this example) in the spocs. Yet if I use the same
call through ExecuteNonQuery() and my sproc calls RAISEERROR more than once
(severity level 11), I get all raises in sqlErrorCollection. Cmon....why the
inconsistent behavior? Just because someone does Fill()...doesn't mean the
script is doing otherthings than fetching rows...I would like more
information from my sprocs than just the original unique constraint violation
(as an example)...hence the many RAISERRORS through the sprocs and subsprocs
I have coded...

Is this a feature or bug? Why is the behavior not doumented? Any thoughts or
solutions or fixes?
 
I wasn't aware of this behavior but I'm going to try it out. As an FYI, you
are definitely looping through the collection right? Otherwise I see why
this would happen. Also, as anotehr FYI, you can use InfoMessage of the
command object to get info from lower severity messages instead of
RasieError but I know this isn't what you asked. Let me try it out and see
if I can find out why.
 
W..

Thanks for the reply..I am definately looping through the collection...you
will only see one error in the collection with a .fill() call from sprocs
that raiseerror more than once. It was something I wasn't expecting either
seeing that errors are returned in other clients correctly (i.e. sql
query)...maybe something in the way datareader decides to put together the
error collection? Because of the sproc pattern of public/private and error
sproc I happen to be using the same level so I decide not to through
informational because I did not want an event for informational messages but
make the decision based on sqlerrors of what to do and use it like a mini
stack trace at the sproc level....let me know what you find...it is
interesting
 
Hi,

I tried both ExecuteNonQuery and Fill on my machine. However, in both
cases, 2 Errors are in the SqlException object. Here is my stored
procedure and .NET C# code.

CREATE PROCEDURE dbo.test2 AS
RAISERROR ('Error1', 11,1)
RAISERROR ('Error2', 11,1)
SELECT * FROM Table1
GO

Fill

SqlCommand cmd = new SqlCommand("test2", this.sqlConnection1);
cmd.CommandType = CommandType.StoredProcedure;

try
{
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
}
catch(SqlException ex)
{

}
finally
{
this.sqlConnection1.Close();
}

ExecuteNonQuery

SqlCommand cmd = new SqlCommand("test2", this.sqlConnection1);
cmd.CommandType = CommandType.StoredProcedure;
this.sqlConnection1.Open();
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException ex)
{

}
finally
{
this.sqlConnection1.Close();
}

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
OK...

Since I have a huge framework in place.......I set the method, in my
framework, that composes a sql script to basically call a test sproc like
yours and pretty much force a .fill() in the way you do it in the example you
gave. I get the result of only one sqlError in the collection......

So...I then created a new vb project that did exactly what your example did...
one sqlError from .fill() and 2 sqlErrors from .ExecuteNonQuery())

I then did the project in C sharp (shouldn't matter I know but I've seen
things)....same results as the above VB Project...


BTY....I am running sql Server 2000

Something is fishy here...what gives?
 
Hi,

I'm not quite sure why this happens. It works fine on my machine. You can
also try to use SQL Profiler to see if both error has been raised in SQL
server.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin,

OK we have to think here....
I know the raiseerrors in your example sproc work...running it in sql
analyzer produces the two errors, and the fact that I get two errors with a
NonExecuteQuery means it is not, or should not be SQL Server. That leaves SQL
Data Provider...are there any hot fixes that address anything in this code?
Any changes in service packs that may have not merged with our VS 2003
versions (every person here running this code gets the same result)...I need
to get this resolved...I have done a search on the web and another guy is
getting this same problem and he does not understand nor does he have a
solution....so their is some nuance here... I need to have this passed around
MSFT for some thoughts....I've been doing this for 20 years and am pretty
sure of my capabilites and that I have covered all my bases....their is
something in a configuration or setting or change that is not propagated to
my version of SQL Data Provider Client.....I need a next step

P.S. I will run Profileer just to make sure but I know I will be seeing it
come across

Thnx
 
Kevin....

Upon further testing...on more machines it seems that some machines are
working the way they should. Armed with this information I started to look at
the differences...the stations that it works on have .NET Framework 1.1 SP1
installed...I installed the SP and low and behold it works...MSFT...this will
be a bug and should be known to be fixed in SP1

Thnx
 
Hi,

Thank you very much for your feedback. I will try to contact the product
team to make it documented. Currently, since it has been fixed in the sp1,
please apply .net framework 1.1 sp1 to all your machines through Windows
Update. Thank you!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top