SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange
Hi everybody,
We were developed some Web-Services that run against SQL Server 2000 with SQLDataReader that executing Stored Procedures in the DB. Most of time (95%)
everything is running well, but sometimes (when exactly, we don't know) the same WebService that worked 200 times, fail.
The code is simple:
| |
publicstring myFunction(int ANumber )
{
string strRetVal =null;
SqlDataReader myReader =null;
SqlConnection MyConn =null;
conststring SAME_STORED_PROCEDURE_NAME_ALWAYS = "sp1";
conststring SAME_PARAM_NAME _ALWAYS = "param2";
conststring SAME_FIELD_NAME_ALWAYS = "RUSureYouWantThisField";
conststring MY_CONNECTION_STR = "Connect me to SQL...";try
{
MyConn =new SqlConnection( MY_CONNECTION_STR );
SqlCommand MyCMD =new SqlCommand( SAME_STORED_PROCEDURE_NAME_ALWAYS, MyConn );
MyCMD.CommandType = CommandType.StoredProcedure;
SqlParameter myParm = MyCMD.Parameters.Add( SAME_PARAM_NAME_ALWAYS, SqlDbType.BigInt );
myParm.Value = ANumber;
MyConn.Open();
myReader = MyCMD.ExecuteReader();
while (myReader.Read())
{
strRetVal = myReader[SAME_FIELD_NAME_ALWAYS].ToString();
break;
}
return strRetVal;
}
finally
{
if (myReader !=null && !myReader.IsClosed)
myReader.Close();
if (MyConn !=null) MyConn.Close();
}
}
|
The line marked in
greenthrows an exception:
System.IndexOutOfRangeException:RUSureYouWantThisField
at System.Data.Common.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at myFunction()...
From the exception it seems that the field nam
e "RUSureYouWantThisField" is not part of the SELECTed fields of the stored procedure. BUT: we sure that we didn't touch that table, nor the stored procedure. Moreover - most of time this function finishes its execution well, so we have no clue what going wrong in the rare times it doesn't work. We Googled it, search this forum and other forums and newsgroups, but we have found only one KB article describes very similar problem when reading field using JDBC. (KB838610,http://support.microsoft.com/default.aspx?scid=kb;en-us;838610).
But:
- We deal with SQLClient, not with JDBC.
- The latter article suggest workaround to this problem - to explicitly specify the fields in the SELECT statement of the stored procedure. our functions failed either when called to stored procedure with "SELECT * From ..." or when called to stored procedure that SELECTed specific field names.
Did anyone encountered this strange behavior ?
Thanks in advance,
Yinon
[4504 byte] By [
Yinon] at [2007-12-28]
Yinon,
I have the same suspension as yours that the sp doesn't return the field "RUSureYouWantThisField" field on some cases (either on special value of input parameter or db/table issue). Could you try calling myReader.GetSchemaTable() and logging the schema before reading the field? This may help you identifying the issue.
Thanks,
Kevin
Hi ,
Even i am facing the same problem, could any body give a solution for that.
thanks in advance
Bali Reddy
This happens on my project almost every day but the code that actually reads from the DB hasn't changed in over 2 years, but now randomly throws these errors for no apparent reason.
There's no real pattern and 99% of the time everything works as expected, but the other 1%, these GetOrdinall() IndexOutOfRangeException's occur.
Would really like to know what's going on. Is this an ADO.NET 2.0 error or a Sql Server 2005 error?
My custom is really getting on my case about this and it was me that recommended investing the money to upgrade to ASP.NET 2.0 and Sql Server 2005 and it's starting to get embarrasing.
Ben
There are a couple of possibilities:
1) The field name isn't being returned from the stored procedure call (as a couple of people have mentioned).
The best way to determine this problem is to actually enumerate the fields and compare the names manually, either in a debugger or by printing them out to a log or error message. In the latter case, I'd suggest only enumerating & printing the fields when the exception occurs by putting in a catch block that explicitly catches IndexOutOfRange errors.
2) Field name differs subtly, and the comparison is sensitive to the difference (i.e. case sensitivity). Field name comparisons are done based on the server's collation. New to Sql2005 accessed from ADO.Net v2 is that changes in language at the server will be reflected in the name comparison on the client. This is really a sub-case of my first listed item, so use the same method to determine if it's a problem, but look for ANY differences between the field name and the name you are looking up.
3) A bug in ADO.Net. This area has been fairly heavily tested, but it's always possible. :-(
If you've carefully ruled out 1 & 2 as possibilities, please post a precise scenario showing working stored procedure and client code that demonstrates the problem.
Hi Alazel
Thanks for the advice. I'll add enumeration code so that I can see the field names being returned.
The real confusing issue is that 99% of the time these fields are returned fine which makes it very difficult to recreate the error. I've tried isolating the code and running it with 20+ concurrent users and it will on occasion fall over, but very rarely, and there doesn't seem to be any pattern. The same offending field names keep popping up, but the error happens inconsistently.
Don't know if this is significant but my db was originally Sql Server 2000 and we recently went over to 2005. These errors have only been occuring since then. We've installed the latest service pack BTW.
A lot of my views contained "select *" statements with "joins" to other tables. I've resaved all my views using Sql Management Studio so that the full field names are specified instead of "*". I haven't had the error occur on my test system since then, but admittedly, haven't had time to do the same stress testing I did yesterday. Could this be it?
If this turns out to be a fix, i'll let you know.
Ben
Changing views from '*' to manual field enumeration could have some effect, but mainly if you've mis-matched fieldname casing in you ADO.Net code on a case sensitive server (or similar comparison issue), and your view now modifies the name. If you specify the view column names precisely as they appear in the tables, though, it shouldn't have made a difference.
I'm pretty suspicious of the comparison style mismatch possibility, especially since the problem turned up after moving to Sql 2005. Did you update the client's to ADO.Net v2 at the same time, or were you already running v2 clients prior?
Hi Alazel
I can see your point about the comparison style mismatch but why would this work most of the time, but then randomly fail, and then carry on working. It makes no sense. As far as I can tell the only factor that varies is the load on the server.
The same stored procedures, and C# code are being executed time after time, and as mentioned, work fine most of the time. It's just the random nature of the IndexOutOfRange exceptions that are really making this difficult to diagnose.
Our entire project went over to Sql Server 2005 and ADO.NET 2.0 at the same time so can't comment about the situation running as ADO.NET 2.0 and Sql Server 2000.
Ben
The intermittant nature definitely makes things difficult to diagnose acurately. I've seen this type of behavior when the stored procedure actually has conditional logic switching which statement actually returns the results, and the comparison error happens in one location, but not the other.
Having behavior vary depending on server load is interesting. Does the problem ONLY occur when the server is under a heavy load? Sometimes, when the server is under a strain and resource failures start happening, things can get a bit tricky with returned error codes, sometimes defering the actual error to the next resultset (it wouldn't show up until a NextResults() call). Is it possible the server actually returned a different error that just hasn't been thrown yet?
One other thing to check for -- before indexing into the reader to get the value, check the FieldCount. If there are no Fields (or significantly fewer than expected), this could be the issue.
I suspect that the SP is not returning a rowset in some (rare) cases. Try adding a
myReader.HasRows
test to see if there are any rows before attempting to access the row values.
hth
You should create a function to return a datareader, and when using, bebore calling the sequence
while (datareader.Read())
{
}
you should check as the following code
if (!datareader.HasRows) return;
*************************************************
I also create a function that call store procedures in SQL, but I write in VB.NET
You can change it to C# easily, by replacing this module in to class
Imports System.Data.SqlClient
Module ConnectSql
'name of the database server
Public Server As String 'name
'database name
Public Database As String
Public UserID As String
' connection object
Public con As SqlConnection
#Region "Manage the connection existed or not"
Public Function OpenSqL() As SqlConnection
If Exist() = True Then
Return con
End If
Try
Dim conString As String
conString = "data source=" & Server & ";initial catalog=" & Database & ";persist security info=False;user id=" & UserID & ";pwd=" & Password
con = New SqlConnection(conString)
con.Open()
Catch ex As Exception
con = Nothing
End Try
Return con
End Function
Public Sub CloseSql()
Try
If con.State <> ConnectionState.Closed Then
con.Close()
End If
Catch ex As Exception
Throw ex
End Try
con = Nothing
End Sub
Private Function Exist() As Boolean
If con Is Nothing Then
Return False
Else
Return True
End If
End Function
#End Region
' this function is used to call Store procedures
' after calling, it return a datareader
'optional paramenters ( parameters of the store procedure called)
'optional value values of each parameter
Public Function SP_Select(ByVal sp_name As String, Optional ByVal params() As String = Nothing, Optional ByVal values() As Object = Nothing) As SqlDataReader
Dim dr As SqlDataReader = Nothing
Try
Dim command As SqlCommand = New SqlCommand(sp_name, OpenSqL())
' set type of command
command.CommandType = CommandType.StoredProcedure
' check if the parameters are passed into the function
If (Not params Is Nothing) Then
Dim len As Integer = params.Length - 1
Dim i As Integer
For i = 0 To len
command.Parameters.Add(New SqlParameter(params(i), values(i)))
Next
End If
dr = command.ExecuteReader()
Catch ex As Exception
MessageBox.Show(ex.ToString)
dr = Nothing
Throw ex
End Try
Return dr
End Function
End Module
We are also seeing the same thing with a web application.... the vast majority of calls to the database work fine, and then it will just start happening. Once we get into this error state, all calls to the database fail with the same IndexOutOfRange Exception. We will see different column names in the exception depending on what table the reader was attempting to use. We recover by restarting IIS.
Our environment is ASP.Net 1.1 on x64 2003 Server SP1 using a SQL2005 SP1 database on another x64 2003 Server SP1. (IIS 6 is running in 32bit compat. mode for our .Net 1.1 web app)
For now our only solution is to use an external monitoring program to detect this condition and restart IIS.
If you can follow the steps I posted previously to capture the exception and log the field names included in the reader for comparison with the string you are using to index, that might help sort out the problem.
If you can do this, please also log the exception details (message, call stack and inner exception details, if any). If you can post these details, I can take a closer look at the code to try to narrow things down a bit.
Barry,
I am experiencing the exact same symptoms in a web app. Once the first error occurrs, no reader seems to get back the column names that are expected and recycling the app is the only way to clear it up. The problem appears to occur randomly and very rarely (3 x in the last 2 months).
My environment is asp.net 1.1 on x64 Win2K Server SP4 (IIS 5) using a SQL2000 db on another x64 Win2K Server SP4.
Considering that our commonality is asp.net 1.1, I am suspecting a problem with that. Further, since it appears to affect everyone, I am wondering if there is a problem in the connection pool since that is the only real commonality between the various readers. What is your opinion?
