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:
public string myFunction( int ANumber )
{
string strRetVal = null;
SqlDataReader myReader = null;
SqlConnection MyConn = null;
const string SAME_STORED_PROCEDURE_NAME_ALWAYS = "sp1";
const string SAME_PARAM_NAME _ALWAYS = "param2";
const string SAME_FIELD_NAME_ALWAYS = "RUSureYouWantThisField";
const string 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 green throws an exception:
System.IndexOutOfRangeException: RUSureYouWantThisFieldFrom the exception it seems that the field name "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.
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()...
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.
Thanks in advance,
Yinon
Answers
- Just as an experiment I wrote a quick multi-threaded app to replicate the problem with static SqlConnections.
Very quickly I am able to generate the target exception on my quad proc 64-bit machine.
The key find from this experiment is that your specific code in one place may be safe but other code in the same process that is not safe can corrupt the state of SqlConnections in the pool. If you run the code sample you will note that "safe" threads are getting the IndexOutOfRangeExceptions.
Hence a probable cause of these issues is some other code in your process that is improperly using pooled connections over multiple threads.
A sugggestion I made earlier to solve this issue (or at least isolate it) thus still holds: Add Application Name=X to your connection strings to "partition" your pools. You can use any value you want for X, you can use the current thread's ID for example to partition pools by thread id, you can use filename, you can use function name, etc... Once you partition then you can isolate (divide and conquer) which remaining parts of the code are corrupting the pool and the rest of your code that uses other pool partitions will be 100% safe from the bad code.
You will note from the code example below, only one thread in 5 will share the global connection globalConn. Each thread runs a different select statement based on the thread id and then attempts to read the field name expected. You will need to modify the connection string to point to your SQL Server but the sample does not rely on any pre-created tables to run (it just selects expression value).
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading; using System.Data.SqlClient; namespace PoolCorrupt { class Program { static void Main(string[] args) { int threadCount = 100; for (int i = 1; i <= threadCount; i++) { new Thread(new ThreadStart(Program.WorkerThread)).Start(); } } static int tidCounter = 0; static UInt32 value = 0; static SqlConnection globalConn = null; static SqlConnection GetConn(string connect, bool useGlobalConn) { SqlConnection connLocal = null; if (useGlobalConn) { connLocal = globalConn; if (null == connLocal) { connLocal = new SqlConnection(connect); connLocal.Open(); globalConn = connLocal; return connLocal; } else { return connLocal; } } else { connLocal = new SqlConnection(connect); connLocal.Open(); return connLocal; } } static object consoleLock = new object(); static void WorkerThread() { int tid = Interlocked.Increment(ref tidCounter); string select = string.Format("select {0} as [f{1}]", tid, tid); string fieldName = string.Format("f{0}", tid); string connect = "server=MySqlServer;user id=test;password=test;"; int readCount = 0; bool useGlobalConnThread = (0 == (tid % 5)); SqlConnection conn = null; SqlCommand cmd = null; SqlDataReader dr = null; for (;;) { try { conn = GetConn(connect, useGlobalConnThread); cmd = conn.CreateCommand(); cmd.CommandText = select; cmd.CommandTimeout = 5; dr = cmd.ExecuteReader(); dr.Read(); int fieldValue = (int)dr[fieldName]; System.Diagnostics.Debug.Assert(fieldValue == tid); readCount++; if (0 == (readCount % 2500)) { Console.WriteLine("Thread{0} reads{1}", tid, readCount); } } catch (IndexOutOfRangeException iorEx) { lock (consoleLock) { Console.WriteLine("Caught targeted exception IndexOutOfRangeException:"); Console.WriteLine("expected -> {0}", fieldName); Console.WriteLine("actual -> {0}", dr.GetName(0)); if (useGlobalConnThread) { Console.WriteLine("Ok, I'm a bad thread and I'm sharing SqlConnections, I know this is going to happen."); } else { Console.WriteLine("Hey, I'm a safe thread and just a victim!"); System.Diagnostics.Debug.Assert(false); } } } catch (Exception ex) { lock (consoleLock) { Console.WriteLine(ex.Message); if (useGlobalConnThread) { Console.WriteLine("Ok, I'm a bad thread and I'm sharing SqlConnections, I know this is going to happen."); } else { Console.WriteLine("Hey, I'm a safe thread and just a victim!"); } // Reset globalConn on failure. SqlConnection connLocal = GetConn(connect, false); SqlConnection connToClose = globalConn; globalConn = connLocal; try { connToClose.Close(); } catch (Exception) { }; } } finally { if (!useGlobalConnThread) { if (null != conn) conn.Close(); } if (null != cmd) cmd.Dispose(); if (null != dr) dr.Close(); conn = null; cmd = null; dr = null; } } } } }
- Marked As Answer byMatt Neerincx [MSFT]ModeratorTuesday, June 30, 2009 9:47 PM
- Hi Marzban,
Before scenario:
After scenario:class MyHandler: IHttpHandler { //the connection object is only instantiated once private static SqlConnection cnn = new SqlConnetion("conn string"); public bool IsReusable { //This is Important! get { return false; } } public virtual void ProcessRequest(HttpContext context) { cnn.Open(); try { ExecuteAQueryAndReadResults(); } finally { cnn.Close(); } } }
class MyHandler: IHttpHandler { public bool IsReusable { //This is Important! get { return false; } } public virtual void ProcessRequest(HttpContext context) { //Connection object is instantiated each time SqlConnection cnn = new SqlConnetion("conn string"); cnn.Open(); try { ExecuteAQueryAndReadResults(); } finally { cnn.Close(); } } }I am still not sure if the problem is gone.- Marked As Answer byMatt Neerincx [MSFT]ModeratorTuesday, June 09, 2009 4:38 PM
All Replies
- 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
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?
Ok, I got it to happen again with enough debug code.
What is happening is that the reader is filled with data from the wrong table. It would seem to be a caching/pooling problem in ado.net where the reader or the reader's content changes beneath me.
This is what I am throwing after catching the exception in GetOrdinal(string name) (I'm creating a new exception of the same type with additional info):
===================================================
Exception Occurred:
System.IndexOutOfRangeException:GetOrdinal failed: IsClosed=False FieldCount=4 (field 0 is Grouping_id) (field 1 is Grouping_description) (field 2 is Grouping_visible) (field 3 is Grouping_name)
at SfData.SfDataReader.GetOrdinal(String name)
at MsWebsite.Database.SearchHomeImpl.ReadOrdinals(IDataReader reader)
at MsWebsite.Database.SearchHomeImpl.FindMultipleByDml(DmlSelect select, SearchOrderBy searchOrderBy)
InnerException: System.IndexOutOfRangeException:Search_id
at System.Data.Common.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at SfData.SfDataReader.GetOrdinal(String name)
===================================================The fields should have been: Search_id, Search_search, Search_submitdate, Search_views. When this starts happening, we always get a reader filled with the wrong table, but the contents can be from different tables (it is just always different than the table we asked for)
Bug?
This is not due to stored procedures because we do not use them... Here is the abbreviated code being used:
IDataReader reader = null;
string select = "SELECT TOP 10 Search.id AS Search_id,Search.search AS Search_search,Search.submitdate AS Search_submitdate,Search.views AS Search_views FROM Search ORDER BY Search.submitdate DESC";
reader = myConnection.ExecuteQuery(select);
if(!m_OrdinalsSet)
{
m_idOrdinal = reader.GetOrdinal(Search_id); //crash here
m_searchOrdinal = reader.GetOrdinal(Search_search);
}- Since we ported our solution to .net 2.0 and sqlserver2005 we've seen this happen on rare occations aswell. Did anyone come up with a solution for it?
- Given the names of the columns you are seeing in the actual reader (grouping_xxx), this looks like a problem with a compute-by statement related to the connection (summary rows being returned by the client). Is it possible that a compute-by is being appended inside your ExecuteQuery data layer method? If not, there may be a bug with compute-by handling that spills between uses of a connection in the pool.
In any case, identifying when the grouping_xxx columns are returned is the next step to identifying where the trouble is. SQL Profiler is probably the best tool for that. Once the exception is thrown, locate the problem query in the trace. If it doesn't have a compute-by added, search back on the same spid to see what was the next prior couple of statements executed. Since this seems to be associated with connection pooling, I suspect you will see an sp_resetconnection call just prior to the failing query, and possibly some transaction-related calls. What you're looking for is a query that specifies the grouping_xxx column names, probably in a group-by or compute-by.
If you know from your code where those columns are projected, you may be able to set up a simpler repro scenario by using just the two queries involved. - We do not add any compute-by statements to our queries. After looking at the SQL Profiler, here are the queries in question from the profiler trace:
SELECT TOP 10 Search.id AS Search_id,Search.search AS Search_search,Search.submitdate AS Search_submitdate,Search.views AS Search_views FROM Search ORDER BY Search.submitdate DESC
SELECT Grouping.id AS Grouping_id,Grouping.description AS Grouping_description,Grouping.visible AS Grouping_visible,Grouping.name AS Grouping_name FROM Grouping WHERE
(Grouping.visible = 1)
These are in SQLBatch events and there are sp_resetconnection RPC calls between every SQLBatchStarting/SQLBatchCompleted pair.
Correct me if I'm wrong, but I don't think the profiler on the db server will show me the problem if it is indeed within the client pool and our web application. Restarting the web app fixes the problem, so I assume the database server is returning the correct results for a given SQL query, but my app is receiving the wrong result set from the pool.
Regardless, I have the Profiler running on my DB server and a packet sniffer setup to capture everything on the wire between the web and db servers. I will post again when I get another failure event. - From the information you've given so far, it's extremely likely that this is indeed a client-side bug. You're seeing metadata from one result showing up in a reader that should be associated with a different result, and you've verified that the results are supposed to show up on different connections. Can you contact CSS to open a case file and have them help you capture the full information we'll need to locate the problem?
Hi Alazel,
We are getting the same error. This happening during the peak timing of our business. We already opened ticket with Microsoft Premium service. Still not able to figure out why this is happening. If you come across any solution or fix please let us know.
Here is more information about our system.
Exception type: IndexOutOfRange
WebServer: Microsoft Windows Server 2003
RAM: 4GB
SQL Server: Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
May 13 2005 18:33:17
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)Thanks,
Zahir
- If I see a fix or workaround for incorrect column names coming from the reader, I'll post a link on this thread. Likewise, if you can, please post the general result of your case (KB article if there is one generated).
- Do you have a ticket id (or something similar) where we can follow the progress of this problem?
Hi Arnebjarne,
I can not post the Ticket ID, but if you are from Microsoft and you need it please send a email to zahirhas at hotmail dot com
So far Microsoft not able to simulate or even we not able to simulate this problem, it happens occasionally and mostly during Business peak hours.
Thanks,
Zahir
- I am experiencing the same error but under different circumstances.
I am specifically trying to pull out a column by the name and I encounter an error. I am catching the error and writing out all the field names in the datareader to a file. The only columns that should be in the datareader are COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT but I am getting a whole list of columns from the other database tables.
If anyone hears anything from Microsoft or finds a work around please let me know. email me at jblackwell at iw-t dot com.
Thanks
-Jason I am experiencing the same problem when the server is heavily loaded.
ASP.NET 2.0, MS SQL Server 2000
Few updates:
Microsoft recommends not passing the SqlDataReader to another method and avoiding any static methods usage, they are not sure this is the root cause to this Exception.
Now when this Exception happens I am logging the SqlDataReader Schema Table also. When I look at the Schema table during the Exception it’s pointing to wrong result.
Thanks,
Zahir- The only thing that I am supicious of is that this wasn't an issue until recently. It never happened until a few weeks ago and now it happens all the time and nothing has changed in the code.
Hi,
I am also having the same problem in my application. The application works most of the time but it fails intermittently. I tried to list all the column names available in the SqlDataReader object. When it works, the data reader seems to have all the columns in it, but when it fails the data reader seems to be missing the last column in the SELECT query. I tried restarting the application, restarting IIS, but the issue still remains. The application is written in .NET 2.0 and the databases is SQL Server 2005. Please let me know if anyone finds an answer to this issue.
Many thanks
Shameer
- I am having the exact same problem. I'm using ASP .Net 2.0 and SQL Server 2000. I have no idea what can be causing this issue, but I would like to get information about what can I do to solve it! This is a critical error on the enterprise application, and it's not happening A LOT.
Thanks,
skullfire HI,
I had the same problem but fortunately it is fixed now. The issue was with one of the .nettiers settings (My application uses codesmith generated code using .nettiers templates for .NET 2.0). When code is generated, codesmith creates a procedures.resources file which is embedded into the application assemby by default. Then .nettires uses a setting in it's configuration file (UseStoredprocedure="true | false") to execute the stored procedures from the database or read the procedure from procedure.resources file and execute it. So if the above setting is 'false' (which is by default), .nettiers reads the sprocs from assembly (which may not be the latested updated one). Changing the setting to 'true' fixed the error. But this still does not explain why the application was working most of the time and fails only intermittently.
Regards
Shameer
Hi,
Did you get a chance to contact Microsoft Support to resolve this, if so please let me know if you have any updates. Some time back we called the Microsoft premier support but did not get any resolution they simply blamed our code, but it works well most of the time. The only time it seems to happen is when the server load is high. You can reach me at zahirhas at hotmail dot com for more info.
Thanks,
Zahir
- I believe this is ADO.NET related and nothing to do specifically with .nettires. But please keep us updated if you find anything resolves this issue.
Dear all
We are getting this error too. It is totally random, although turning connection pooling off reduces the frequency of the error. We are using SQL Server 2005 and ASP.net 1.1. Frequency seems to increase with load - we are convinced it is an ADO bug. Have any of you had a response from Microsoft or have found a solution?
Regards
Phil
OK - think we've cracked it..
Our ASP.net v1.1 code was originally using the Data Access Application Block to access our SQL Server. We've replaced this and now we manually create connection, command and dataset objects and the problem seems to have resolved itself. Not sure if you guys were/are using the code block but it seems that it is that which is causing the problem.
Comments please.
Phil
Hi,
The problem I am having is very similar although I don't get an IndexOutOfRange error. I get the following call stack:
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at GTS.ComAppLibrary.LXRecord.ReadRecords(SqlDataReader _dr)The error does not happen all the time. The code that uses the GetOrdinal is part of a service written in the 2.0 framework. The service loops ever couple seconds. It is very hard to track down because this application is distributed to our customers and I can't get the problem to happen in my test environment.
We are not using application blocks for data access. We are using:
SqlConnection
and SqlCommandMy work around at this point is to make my own enums for the ordinals, but this is a pain and I can't understand why this problem is still going on. This thread goes on for 2 years!
It seems the only thing in common is using ado.net and maybe it is worse under load and that is why it is so hard to reproduce.
Has MS responded? Should I create a new thread?
Thanks
Randy
We also using Data Application Block. We come up with the following workaround after that, the error not yet happened for the past two weeks.
If you are running out of process on your website you should be able to set the Application Pool Recycling to recycle every 15 minutes. This has helped us avoid the GetOrdinal error however now we are receiving occasional web erros when the recycle process forces a shutdown. Please post your experiences with this configuration change.
Thanks,
Zahir
- Has any solution been found on this matter, as I am experiencing the same exact problem.
- Just to let you guys know that I've solved the problem.
The problem was that my dataAccess connections and methods were defined as STATIC, which means that sometimes the datareader was receiving data from other tables.
I changed that and everything is now ok. Zyad wrote: Just to let you guys know that I've solved the problem.
The problem was that my dataAccess connections and methods were defined as STATIC, which means that sometimes the datareader was receiving data from other tables.
I changed that and everything is now ok.Can you explain what you mean as STATIC? And how you changed your code to fix this issue?
Thanks
Sorry that did not solve my problem.
It started re-appearing after a couple of days.
It seems that this happens in 2 cases : (but it's so hard to be able to really pin point the problem)
1. When there's a peak on the server
2. When there's a connection timeout with the database.
But that problem is very strange, as it started to appear when I migrated my application from 1.1 to 2.0.
Anyone from Microsoft can help ?
That's my exact experience as well.
It appears to me that there is a "bad" connection in the connection pool. Due to the "timeout" error, my normal connection closing routine is bypassed. Once this happens, other functions try to use this function. If you are referencing a column by name, you get the IndexOutOfRange error. If you are referencing by index, you will get typically get a type conversion error.
I just migrated all my code from ASP/VB6 to asp.net 2.0.
If I were to guess, the connection that had the timeout is made available to the pool again but is still actively running the query that timed out. I know my code doesn't close the connection when the error occurs. I try to explicitly close every connection in my application when the transaction is complete. The only time this doesn't happen is with errors at this time.
- You should use exception handling to ensure your connection is closed even if there is a timeout or other error. However, I seriously doubt that the connection is being returned to the pool while still actively running a query. Not to mention that I timeout would immediately kill the query.
The only way we could prevent this error was to turn connection pooling off. Not a great idea in itself but not a problem for us as we have a limited user base and restart our application out of working hours. We are convinced it is a pooling problem.
I plan to add the exception handling in the next round of updates to the webapp. During the conversion I disabled all the "on error" routines (good old vb) to capture all small conversion issues. It's a daunting task since I will have to touch almost every bit of code.
But it's hard to explain the crossed data and invalid recordsets otherwise. How does my code run rs.executereader, then iterate the resultset and not find the columns?
- Please, please first disable connection pooling and see if that resolves your issue. If it does and and we can get more to collaborate this then maybe someone from Microsoft will start to look into it.
I have turned off connection pooling.
Back in a few day to post my observations.
- We are experiencing this exact same problem very frequently lately. The error gets logged in our our event viewer and always identifies a column which is the first column expected from the DataReader. This leads me to believe that the row is empty in the DataReader. However in reviewing the code we are using standard if dr.read() then ... logic prior to referencing the DataReader column values. Other specifics of our environment are we using SQL Server 2005 ( a recent migration ) and a single IIS 6.0 website instance which is handling 8.5+ million requests per month ( so relatively heavy load ). I never expected that using dr.HasRows was a firm requirement to using DataReaders in ADO.NET ( especially since it did not exist in ADO.NET 1.0 ) - but it looks like this is the only reasonable advice offered so far on this topic.
Lee wrote: I have turned off connection pooling.
Back in a few day to post my observations.
Day one with connection pooling off, these errors have not occurred.
- hmm.. interesting, please give it a few more days tho just be sure.. incidentally are you using the DAAB?
Patch79 wrote: hmm.. interesting, please give it a few more days tho just be sure.. incidentally are you using the DAAB? I'm not familiar with DAAB.
- Yes we are using MSDAAB. And turning off connection pooling is a no-go for our site - given the amount of traffic we are supporting.
I've turned pooling off.
It's been 3 days now, and I haven't had a single error.
However, turning pooling off, is not something that is recommended. There must be something else going on that is causing the connections to act this way.
I haven't had this error since I turned off connection pooling.
I can't say I've been as fortunate as Zyad with no errors though. But these errors are my fault. =)
- Typically, an out of range failure is because the object being refrenced is not populated with data. For example when your routine to fetch the DataReader fails to return any rows. This might be because there are no rows to fetch (thus HasRows is essential) but it could be that the routine failed, trapped the exception and returned without indicating an exception to the calling routine. If turning off the Connection pool fixes it, I suspect faulty exception handlers somewhere down the call chain. For those that have tried turning off the pool, check to see if the number of connections being orphaned at the server has increased... I expect they have. If this is the case, you've delayed the inevitable failure of the server to accept additional connections.
William Vaughn wrote: Typically, an out of range failure is because the object being refrenced is not populated with data. When my code referenced the data reader by index, I would get type conversion errors rather than index out of range errors. Whatever corrupted the connection has already happened at this point.
Example one:
The error description is as follows: System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.IndexOutOfRangeException: theValue
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at o2busRegRead.GetSetting(String sKeyName, String sValueName) in D:\webfiles\oxygen\App_Code\RegRead.vb:line 90
The code:
Dim rs As SqlDataReader = c.ExecuteReader
If rs.Read = True Then
theValue = rs("theValue")
Else
theValue = ""
End IfExample two:
The error description is as follows: System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.
at System.Data.SqlClient.SqlBuffer.get_String()
at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at o2busOrder.GetNotes() in D:\webfiles\oxygen\App_Code\order.vb:line 704
The code:
Dim rs As SqlDataReader = c.ExecuteReader
sNote = ""
While rs.Read = True
sNote = sNote & rs(0)
End WhileWilliam Vaughn wrote: For example when your routine to fetch the DataReader fails to return any rows. This might be because there are no rows to fetch (thus HasRows is essential) but it could be that the routine failed, trapped the exception and returned without indicating an exception to the calling routine. If turning off the Connection pool fixes it, I suspect faulty exception handlers somewhere down the call chain. For those that have tried turning off the pool, check to see if the number of connections being orphaned at the server has increased... I expect they have. If this is the case, you've delayed the inevitable failure of the server to accept additional connections. But if the connection is not properly closed, shouldn't an unused connection be selected from the pool when the next .Open is done? And eventually you should run out of connections in the pool, this would support the theory of having orphan connections with pooling off as well.
6 days without this error with connection pooling off.
Are there any updates or further developments on a proper workaround for this?