SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 16 Nov 2005 14:02:03 Z

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: 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 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.

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 09 Dec 2005 23:25:44 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 29 Dec 2005 23:19:07 Z

Hi ,

Even i am facing the same problem, could any body give a solution for that.

thanks in advance
Bali Reddy

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 29 Jun 2006 12:39:10 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 29 Jun 2006 15:47:47 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 30 Jun 2006 14:29:17 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 30 Jun 2006 16:34:13 Z

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?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 03 Jul 2006 11:06:06 Z

Hi Alazel

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 03 Jul 2006 11:11:34 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 03 Jul 2006 20:18:06 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 04 Jul 2006 05:29:38 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 04 Jul 2006 07:59:57 Z

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)))

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 18 Jul 2006 01:01:24 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 19 Jul 2006 19:52:37 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 01 Aug 2006 15:56:16 Z

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?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 17 Aug 2006 02:14:45 Z

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 17 Aug 2006 02:37:18 Z

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);
}

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 13 Sep 2006 15:45:52 Z

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?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 14 Sep 2006 16:02:58 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 15 Sep 2006 02:15:27 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 15 Sep 2006 22:01:01 Z

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?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 19 Sep 2006 17:17:51 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 20 Sep 2006 16:58:38 Z

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).

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 22 Sep 2006 08:00:00 Z

Do you have a ticket id (or something similar) where we can follow the progress of this problem?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 25 Sep 2006 16:08:26 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 29 Sep 2006 13:28:01 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 05 Oct 2006 12:52:39 Z

I am experiencing the same problem when the server is heavily loaded.

ASP.NET 2.0, MS SQL Server 2000

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 05 Oct 2006 15:27:15 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 05 Oct 2006 18:22:41 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 10 Jan 2007 07:22:51 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jan 2007 23:34:46 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Sat, 20 Jan 2007 09:05:20 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 22 Jan 2007 22:34:26 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 22 Jan 2007 22:36:30 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 26 Feb 2007 10:06:52 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 27 Feb 2007 12:19:25 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 01 Mar 2007 15:46:00 Z

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 SqlCommand

My 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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 14 Mar 2007 20:51:08 Z

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

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Sat, 31 Mar 2007 16:35:21 Z

Has any solution been found on this matter, as I am experiencing the same exact problem.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 02 Apr 2007 16:39:07 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 03 Apr 2007 20:07:47 Z

Zyad wrote:

Can you explain what you mean as STATIC? And how you changed your code to fix this issue?

Thanks

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 14:17:21 Z

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 ?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 14:51:26 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 15:30:23 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 15:41:41 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 15:43:47 Z

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?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 15:49:41 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 18:36:10 Z

I have turned off connection pooling.

Back in a few day to post my observations.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 12 Apr 2007 23:15:55 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 13 Apr 2007 15:43:01 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 13 Apr 2007 15:48:31 Z

hmm.. interesting, please give it a few more days tho just be sure.. incidentally are you using the DAAB?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 13 Apr 2007 16:25:07 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 13 Apr 2007 20:55:32 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Sat, 14 Apr 2007 05:25:08 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 16 Apr 2007 09:18:36 Z

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. =)

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 18 Apr 2007 20:48:33 Z

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 19 Apr 2007 05:30:54 Z

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 If

Example 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 While

William 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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 08 May 2007 16:18:18 Z

Still going.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 10 May 2007 17:26:46 Z

Hi everybody,

I think I’m experiencing your same problem

We developed a lot of WebApplications and our environment is on .NET 2.0 and Sql Server 200o. (All Service packs are always installed)

Most of time (99%) everything is running well, but sometimes (when exactly, we don't know) the same Web Applications that worked 1000 times, fail.

It seems sp doesn't return right fields or something similar. This happens occasionally and, when it occurs, a lot of components have the same problem.

In Event Viewer Warning appear messages like:

System.IndexOutOfRangeException: Site_Uid at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at USNext.CommonUserControls.ContactInfo.GetLoginSites(SqlConnection sqlCnn)

DataBinding: 'System.Data.Common.DbDataRecord' does not contain a property with the name 'name'.

I’m pretty sure that the code and the stored procedure are fine also because they are old and executed a lot of times (more than 10000) every day.

We started to have this type of problem a couple of months ago, initially sometime then every day around same hour (during the morning, when the server ).

One month ago I increased the Max Pool Size setting of connection string to 500 and now it happens one time every 7-10 days. When it occurs I check the connections opened on SQL and they are not a lot.

Now I followed also all Alazela suggestions to track what exactly occurs but I can’t turn off connection pooling given the amount of traffic we are supporting.

The only solution I found:

- Wait a few minutes then everything return to work fine

- Restart IIS

Does someone know how to solve this problem? This post has been opened in Nov 2005 …

Thanks,

Emanuele

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 11 May 2007 15:20:38 Z

I tracked it.

This is my Code:

private void GetLoginSites(SqlConnection sqlCnn)

{

SqlDataReader rs = null;

string __TempDebugMessage = "";

try

{

// Create Command

__TempDebugMessage += "1";

SqlCommand sqlCmd = new SqlCommand();

sqlCmd.Connection = sqlCnn;

sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.CommandText = "Retrieve_Login_Sites";

// Set Parameters

__TempDebugMessage += "-2";

sqlCmd.Parameters.Clear() ;

__TempDebugMessage += "-3";

SqlParameter ContactUidParam = sqlCmd.CreateParameter();

ContactUidParam.Direction = ParameterDirection.Input;

ContactUidParam.ParameterName ="@LoginUid";

ContactUidParam.Value = this._LoginUid.ToString();

ContactUidParam.SqlDbType = SqlDbType.Int;

sqlCmd.Parameters.Add(ContactUidParam);

__TempDebugMessage += " ";

__TempDebugMessage += "EXEC Retrieve_Login_Sites "+this._LoginUid.ToString();

this._SitesDOM = new XmlDocument();

this._SitesDOM.AppendChild(this._SitesDOM.CreateElement("SITES"));

// Execute Reader

__TempDebugMessage += "ExecuteReader ";

rs = sqlCmd.ExecuteReader();

// Load DataReader Main Info

__TempDebugMessage += "FieldCount: " + rs.FieldCount.ToString() + " ";

__TempDebugMessage += "HasRows: " + rs.HasRows.ToString() + " ";

// Load Schema Table

DataTable __rsDT = rs.GetSchemaTable();

__TempDebugMessage += "CaseSensitive: " + __rsDT.CaseSensitive.ToString() + " ";

__TempDebugMessage += "HasErrors: " + __rsDT.HasErrors.ToString() + " ";

// Load Columns

__TempDebugMessage += "FIELDS: ";

for(int __CCounter=0; __CCounter<rs.FieldCount; __CCounter++)

{

__TempDebugMessage += __CCounter.ToString() + " " + rs.GetName(__CCounter) + " [";

__TempDebugMessage += rs.GetFieldType(__CCounter).ToString();

__TempDebugMessage += " - " + rs.GetDataTypeName(__CCounter) + "] ";

}

__TempDebugMessage += " ";

int __CReadCount = 0;

__TempDebugMessage += "ROWS: ";

while(rs.Read())

{

try

{

__CReadCount += 1;

__TempDebugMessage += __CReadCount.ToString() + " ";

int SiteUid = rs.GetInt32(rs.GetOrdinal("Site_Uid"));

__TempDebugMessage += "A";

string SiteName = rs.GetString(rs.GetOrdinal("Site_Display_Name"));

__TempDebugMessage += "B";

string SiteServer = rs.GetString(rs.GetOrdinal("Site_Web_Server"));

__TempDebugMessage += "C";

XmlElement SiteElement = this._SitesDOM.CreateElement("SITE");

SiteElement.Attributes.Append(this._SitesDOM.CreateAttribute("uid"));

SiteElement.Attributes["uid"].Value = SiteUid.ToString();

SiteElement.Attributes.Append(this._SitesDOM.CreateAttribute("name"));

SiteElement.Attributes["name"].Value = SiteName;

SiteElement.Attributes.Append(this._SitesDOM.CreateAttribute("web_url"));

SiteElement.Attributes["web_url"].Value = SiteServer;

this._SitesDOM.DocumentElement.AppendChild(SiteElement);

__TempDebugMessage += "D ";

}

catch(Exception exSP)

{

this._ClassError="GLS1 - " + exSP.Message;

this._ClassErrorString=exSP.ToString() + " " + __TempDebugMessage;

}

catch (Exception ex)

{

this._ClassError="GLS2 - " + ex.Message;

this._ClassErrorString=ex.ToString() + " " + __TempDebugMessage;

}

finally

{

if(rs!=null)

rs.Close();

}

This method went in error and information wrote in debug message are:

GLS01 – Site_Uid

1-2-3

Retrieve_Login_Sites 27, 0, 1

Execute Reader

Field Count: 2

HasRows: True

CaseSensitive: False

HasErrors: False

FIELDS:

0 List_Uid [int]

1 Sequence [int]

ROWS:

1 2 3

Stored Procedure Retrieve_Login_Sites gives me back following fields

Site_Uid, Site_Display_Name, Site_Web_Server

Buy not now. Now I received a result set of another SQLCommand with following fields: List_Uid, Sequence.

This is not the only error we had during the problem.

Can it be useful to Microsoft?

Thanks,

Emanuele

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 11 May 2007 15:51:59 Z

I would add a connection close to your exception logic and see if that resolved your issue.

Do you get any other errors before this error starts to happen? Maybe a timeout error ?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 11 May 2007 18:14:33 Z

Hi Lee,

Thanks for your attention but connection it's opened and closed by caller method and also if an exception occurs it’s correctly closed in a finally {} statement.

I don't really think it’s a code problem also because:

We implemented a very big quantity of code in these years and we manage always exceptions.

Everything works fine and when the problem occurs (randomly) all our libraries have this problem. In a particular point of code we can have an error but not everywhere.

A lot of persons are experiencing this problem, considering also how this thread is old, and I didn’t see a solution introducing exception logic also because an exception logic it’s standard during DB access to close it correctly.

I’m not able to figure out how .net can returns a result set of another SQLCommand called by other code, also a different library, executed for a different user request when a connection it’s not properly closed or a timeout event happen.

You wrote: “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?” I have your same question

Thanks,

Emanuele

Have you turned on your connection pool?

Can “Timeout + connection pool on” give this type of problem under certain conditions also if everything is manage correctly (exceptions)?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 11 May 2007 18:39:52 Z

Emanuele wrote:

Have you turned on your connection pool?

Can “Timeout + connection pool on” give this type of problem under certain conditions also if everything is manage correctly (exceptions)?

I still have connection pooling off and have not had this error happen.

There was usually a timeout error prior to this error starting. I usally had to do a iisreset to correct it.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 15 May 2007 15:10:47 Z

Hi all,

So I've read over this thread. It seems that I'm not the only person with this issue and it's about time that it got sorted. It would be great if our friends in MS got on board and dealt with this issue - 2 years and counting is quite a long time

Our setup:

ASP.Net 2.0 and Sql Server 2005. This is a new issue that presented itself in the last few months. We migrated to ASP.Net 2.0 shortly before.
Comprehensive web-based database application with up 500 users login to the system on a daily basis.
Basically a four layer application architecture: UI -> Business Objects -> DAL -> Database.
Data access layer provides our data access methods such as GetDataReader. It manages the connections to the database, command objects, exception handling etc, and also includes type-safe reader methods.
DataException are caught in the DAL and thrown up the application layers, adding appropriate information as required.

The problem:

This morning we received many error notifications all of which easily recognisable as a missing column in a data reader.
I tried logging into the system and could not - serious problem of down time for our customers.
I quickly realised that to get the system back up and running I would have to restart either SQL Server or IIS. I opted for the former and it solved the problem, for now.
The issue has occurred once before and that time, if I remember correctly, it resolved itself after a while. We cannot afford to let it "resolve itself".
Example of error message:

System.IndexOutOfRangeException:
Message:

OrgID
Source:

System.Data
Targetsite:

Int32 GetOrdinal(System.String)
Stacktrace:

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 Chip.DAL.Utilities.getIntFromDataReader(SqlDataReader dr, String name)

at Chip.BusinessObjects.OrgAddressUser.OrgAddressUser.Read()

Comments

We simply cannot turn off connection pooling - it would cause far greater problems given the size of our application.
The problem may be related to load but given that today was just a normal day (i.e. no extra load) then I suspect this is not the issue.
A number of people who posted suggested restartng IIS. If it happens again I may try that.
I modified our code to trace extra exception details as follows:

If reader.HasRows is true [Note: in our case this should always return true given that we already use reader.Read() which return true if records have been returned]
Atttempt to read the column
Catch the System.IndexOutOfRangeException
Trace out the exception.
Trace out the properties FieldCount and HasRows of the reader object.
Trace the XML representation of the reader [GetSchemaTable()]

If I get the results for this I will post them on this thread. However, given that it has happened only twice in the last few months I don't expect the issue to occur any time soon again.
Also note that there were no new deployments lately so the application state has not been modified in any way to cause these issues.
There is also the problem in trying to resolve this issue as I cannot replicate it and so cannot be sure that any change I make has resolved it. So any change I make to the system will not be done lightly.
Some of the other comments on this thread included:

"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.".

"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."

Surely it makes more sense for the methods to be static? Otherwise the object would have to be instantiated each and every time we want to do a data access.
Please explain how the data reader could be receiving data for other tables?

I suspect that this is a MS issue. Possibly with ADO.Net, connection pooling or even some sort of memory corruption on either SQL Server or IIS. The fact that restarting SQL Server resolved the issue, as well as restarting IIS raises a number of questions don't you think?

Any advice or comments are appreciated.

Cheers,

Martin

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 19 Nov 2007 22:03:38 Z

No posts in over 6 months, has anyone found a resolution to this problem?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 20 Nov 2007 10:08:27 Z

Hi Andrew,

We decided to refactor our data access code such that the SqlDataReader object is no longer being passed into the static reader methods. This has been deployed and we have no problems yet. However, I would not be absolutely certain that this resolved this issue. The orginal problem for us only arose very infrequently - every few months or so - and only since we migrated to .NET 2.0 at the start of this year. I suspect that it may have been a memory issue.

Regards,

Martin

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 20 Nov 2007 16:45:07 Z

We are still running with connection pooling off without any issues.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 20 Nov 2007 17:33:27 Z

Thanks for the replys to this old thread.

Our problem occurs using .NET 2.0, IIS 6, SQL2K. We are seeing this happen about once a week. When it happens it seems to be either caused by or causing a chain reaction where all the users start to loose their session. We can't reproduce this error, we can't seem to pin point it to any specific place in our code or any specific error, we can't even say that this only happens under heavy load. The only thing that is common is it only happens on our prodcution servers and when it happens there are numerous SQLDataReader.GetOrdinal() failing with IndexOutOfRange. Unfortunately, turning off connection pooling isn't really an option with the load that our servers can find themselves under.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 21 Nov 2007 09:42:51 Z

Hi Andrew,

First of all in response to other comments in this thread I can say that in our case turning of connection pooling is also not an option for us for the same reason.

The problems that you are having sound exactly the same as ours except that we do not have the issue as often. The issue was occurring for us every couple of month’s maybe. That means we are overdue a repeat and this has not happened which suggests that we might resolved this issue.

Can I ask how you bring the application back online after these errors occur? If I remember correctly recycling the ASP.NET worker process/application pool stopped these errors from recurring for us.

I also recall that the active worker process visible in the Task Manager had large amounts of memory usage. After recycling the worker process the issue was resolved and new worker process had started. I wonder does this suggest that the issue is memory related corruption possibly related to ADO.NET/Connection Pooling?

I read the following comment:

"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.".

We modified out data access helper classes to ensure that SqlDataReader objects are not passed into any static (or otherwise) methods.

We cannot be absolutely certain that this has resolved the issue but it is likely given that we are overdue a re-occurrence of this issue.

Hope this helps,

Martin

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 26 Nov 2007 20:00:29 Z

Hi Martin

When the problem occurs, we have to fail over to our backup server. We have tried restarting IIS, but that didn't resolve the problem. I haven't tried specifically recycling the ASP.NET pool (wouldn't restarting IIS accomplish the same thing?)

Right now we are in the process of modifying our data access classes so that we are "...avoiding any static method usage." It will take a while before we are ready to deploy those changes.

I'm not sure that the IndexOutOfRange and the massive loss of session are necessarily related. Were you also seeing all of your users suddenly start loosing session? We are also currently in the process of talking directly to MSFT support about this now, hopefully they will be able to get to the bottom of this quicker than we have.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 27 Nov 2007 09:09:30 Z

Hi Andrew,

This suggests that the problem you are experiencing might be different. Or there may be two issues here: the IndexOutOfRange issue which is the one we are experiencing and the loss of session.

We never had any issue with users’ sessions. And you are correct in saying that restarting IIS would restart the application pool.

Please keep me posted on what the MS support team suggest.

Good luck with it,

Martin

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 12 Feb 2008 11:11:57 Z

Hello,

We experienced this problem for the first time today and a quick restart of the website seemed to get things running again (I added a space to the end of the connection string which had two effects - it restarted the website and caused a new connection pool to start). I'm not sure that my manager sees this as a solution to the problem however Are there any updates or further developments on a proper workaround for this?

I see that turning off connection pooling has worked for one person, but if we can avoid this then I would certainly like to...

Thanks

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 19 Mar 2008 17:36:17 Z

Has anyone figured this out yet?

I'm having the same issues. Stable code that's been running for over a year and about 2 weeks ago these problems started happening. I just turned connection pooling off but that in not an acceptable solution long term.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 19 Mar 2008 23:16:36 Z

We had some static methods that were being used for our database calls. After opening up an issue with Microsoft they insisted that any calls to the database should not be inside of a static method. After removing all static methods that had anything to do with the database, the problem has disappeared. I should note that the error messages we saw were not always related to the static methods. However, we haven't had any reoccurrence in over a month.

For now the issue remains open with Microsoft and we’re waiting to see if the problem occurs again. Our web application has cycles of high volume and low volume and right now we’re in a low volume time so the fix hasn’t really been abused.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 20 Mar 2008 14:17:53 Z

What makes something static vs non-static?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 20 Mar 2008 16:49:01 Z

Using the static modifier when declaring a method makes that method static.

For more information on using static methods and classes check this out:
http://msdn2.microsoft.com/en-us/library/79b3xss3(VS.80).aspx

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 20 Mar 2008 17:02:46 Z

Thanks.

What determines static vs non-static in vb.net?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 20 Mar 2008 17:53:44 Z

I've refactored all the code and removed all static methods. I am returning the connection string in a static method but it's just a string. And I'm still experiencing the issues. I turned connection pooling off so hopefully that helps until we can figure out what is causing all this.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 20 Mar 2008 19:25:28 Z

Lee Murawski @o2 wrote:

Thanks.

What determines static vs non-static in vb.net?

Found this link for you, maybe it will help
http://msdn2.microsoft.com/en-us/library/z2cty7t8.aspx

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Sun, 20 Apr 2008 17:51:43 Z

Hi all,
I've encountered the exact same problem, I read the thread thoroughly and I think I understand where the error comes from and how you can solve it (With connection pooling on)
Lets say I have two sql connections, one for fetching data and one for retrieving data (SqlConnection fetchConn, insertConn)
Now lets say I use the insertConn on different thread than fetchConn, what can happen (sometimes) is that the following two lines, on two different threads, will give back the same actual connection!

Code Snippet

void Thread1()
{
fetchConn.Open();
}

void Thread2()
{
insertConn.Open();
}

Because the Open() method goes to the connection pool and the connection string is the same for the two connections (hence the same connection pool), it can return the same connection (SqlConnection methods are not thread safe)
and even though we have two different SqlConnection objects, the pooling mechanism makes it the same scenario as using the same connection object from two different thread.
From now on the behaviour is unexpected and the errors we got seems more reasonable now..
This also explains why turning off the connection pooling solves the problem.
If you do want to use connection pooling, you'll have to understand this issue and fix the thread safety issues resolved from it.
The solution will be specific for each case but a very easy patch you can apply to check if it solves your problem is to change something in the connection string of every connection you use at a different thread, so it will have a different pool.
If you're not using (knowingly) any other threads, you have to check that a thread is not started for you by any lower level infrastructure which makes your code run at different thread.. (some callbacks might be on different thread for instance)
I hope this helps you, if you think it's entirety not the case for you, I'm sorry for wasting your time

Itai.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 04 Jul 2008 01:34:33 Z

I found there is a kb article talk about IndexOutOfRangeException,

http://support.microsoft.com/kb/948176/en-us

But ther is not a download for it.

Anyone have tried it?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 10 Jul 2008 13:52:53 Z

Could somebody confirm that the hotfix works? The description doesn't really match the error everybody is facing here.

And please MS, could someone please pretty please join in. This topic has been lingering without a solution for far too long!

In the mean time I'll be trying the following code:

Code Snippet

lock (String.Intern(conn.ConnectionString))

{

conn.Open();

}

I'll try to post the results after several days of testing if this actually helps.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 29 Jul 2008 15:02:55 Z

The attempted fix I posted earlier does indeed seem to work, not a single IndexOutOfRangeException anymore. I haven't tried the hotfix yet, but it does seem likely that this hotfix will solve the problem.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 30 Jan 2009 19:03:14 Z

This hotfix has been supposedly rolled into .NET 3.5, but we are still seeing the same issue occurring on a fairly consistent basis on our web servers with .NET3.5. As our servers are always under fairly heavy load all day, this error keeps popping up at the rate of 800 to 1000 occurrences per day per server. Btw, we use nHibernate 2.0.1 to access SQLServer2K5 database.

Can anybody shed new light on this IndexOutOfRangeException issue?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 19 Feb 2009 15:48:53 Z

We also having same issue in our production server.

Server OS: Windows Server 2008
Framework : 3.5

We are getting this error on peek hours, and if we refresh the page after some minutes its started working fine.

We are using more SQL queries and stored procedure and i noticed that this error occures only for queries which used alias with tables ( i am may be wrong ).

Anyone found the solution for it.

- Anto Binish Kaspar

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 20 Feb 2009 10:52:33 Z

A client of ours just reported encountering this error.

Our application is a windows application not a web application.
We're using .NET 2.0 and SQL Server 2005.

The application has been running fine for over 4 months and this is the first time we see this error.

    Private Function getID() As Long 
        Dim strSQL As String 
        Dim drsql As SqlClient.SqlDataReader 
        strSQL = "SELECT line_Id from lines where line_Number='" & line_Name & "' and site_id=" & siteid 
        drsql = selectCMD(strSQL) 
        If drsql.HasRows = True Then 
            drsql.Read() 
            getID = drsql("line_Id") 
        Else 
            getID = -1 
        End If 
        drsql.Close() 
    End Function 
 
    Public Function selectCMD(ByVal strSQL As String) As SqlDataReader 
        Try 
            If cnSQL.State = ConnectionState.Open Then 
                Dim cmdSQL As New SqlCommand(strSQL, cnSQL) 
                cmdSQL.CommandTimeout = 180 
                selectCMD = cmdSQL.ExecuteReader 
                'log(strSQL, EventLogEntryType, 123) 
            Else 
                If openConnection() = True Then 
                    selectCMDselectCMD = selectCMD(strSQL) 
                Else 
                    Return Nothing 
                End If 
            End If 
        Catch ex As Exception 
            Dim exString As String 
            exString = "Error in SELECT statement " & vbCrLf & strSQL & vbCrLf & ex.ToString & "." 
            log(exString, EventLogEntryType.Error, 108) 
            selectCMD = Nothing 
        End Try 
    End Function 

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 02 Mar 2009 12:46:31 Z

We are also experincing these errors.

Any update to this other then disabling pooling ? We have now implemented this and will see if the errors disappears. We now that disabling pooling is very bad for performance, but we a re beginning to get desperate, as we have dealt with this for the last 5 weeks, encountering this error 2-3 times a week.

We are using Windows 2008, Sql Server 2008 and .net 3.5

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Sat, 07 Mar 2009 21:19:38 Z

Problem is fixed for me

I did three steps and updated the application to live server before 15 days, still we dint get any errors in live website. We used to get aleast once in a day but now its fixed. Here is my three steps

It was using try, catch and finally to close the connection. i changed to "USING"
I was using static function to create a new connection for eg SqlConnection connection = Util.GetConnection(); i changed it to public function, like this SqlConnection connection = new Util.GetConnection();
I called Dispose() function of SqlReader after Close() function call.

Before

SqlConnection connection = Util.GetConnection();
SqlDataReader reader = null;
SqlCommand command = null;

try
{
    command = new SqlCommand("select a,b,c from blabla");
    reader = command.ExecuteReader();
    while (reader.HasRows)
    {
        //coder here
    }
}
finally
{
    if (command != null)
    {
        command.Dispose();
        command = null;
    }
    if (reader != null)
    {
        reader.Close();
    }
    connection.Close();
    connection.Dispose();
}

After

using (SqlConnection connection = Util.GetConnection())
{
    using (SqlCommand command = new SqlCommand("select a,b,c from blabla"))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.HasRows)
            {
                //coder here
            }
            reader.Close();
            reader.Dispose();
        }
        command.Dispose();
    }
    connection.Close();
    connection.Dispose();
}

Above code is not actual one, its just the format.

Only these changes i did in my project. And errors gone. Please try this solution.

Thanks,
Anto Binish Kaspar

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 16 Mar 2009 16:23:15 Z

Hello everyone,

I have read the topic thoroughly since I experience the same problem as described above. The situation is similar: I do an query to a stored procedure and sometimes get IndexOutOfRange exception. It occurs rarely and only during peak hours. I was unable to reproduce the problem on my developer's environment.

I found the reason why it fails sometimes. The problem is that returned resultset is invalid and has no relation to initial query. Consider this example:

There is a database with 2 tables: one stores information about websites with a default domain name and another table contains additional domain names to each website. I have a stored procedure which fetch website Id by domain name. I call the procedure using pure ado.net without any additional frameworks. Here is the code:

            IDbCommand command = new SqlCommand();  

            command.Connection = new SqlConnection(connectionString);  

            command.CommandType = CommandType.StoredProcedure;  

            command.CommandText = "[dbo].[GetWebSiteId]";  

            dataService.AddDbCommandParameter(command, "hostname", urlName);  

            dataService.AddDbCommandParameter(command, "hostname2", urlName2);  

            object dirtyWebsiteId = 0;  

            IDataReader reader = null;  

            try  

            {  

                command.Connection.Open();  

                reader = command.ExecuteReader();  

                if (reader.Read())  

                {  

                    dirtyWebsiteId = reader[0];  

                }  

            }  

            finally  

            {  

                if (reader != null && !reader.IsClosed)  

                {  

                    reader.Close();  

                }  

                command.Connection.Close();  

            } 

            if (!(dirtyWebsiteId is int)) 
            {
                throw new Exception("It must be int!");
            }

And here is the code of the stored procedure where hostname2 is "www." + hostname:

    SET NOCOUNT ON 

    DECLARE @id INT 

    SELECT TOP 1 @id = ISNULL(w.Id, 0)  

        FROM WebSite w   

        LEFT JOIN WebSiteDomainName wdn ON w.Id = wdn.WebSiteId   

        WHERE w.UrlName = @hostname   

            OR w.UrlName = @hostname2   

            OR wdn.Url = @hostname   

            OR wdn.Url = @hostname2      

    IF (@id IS NULL)  

    BEGIN 

        SET @id = 0  

    END 

    SELECT @id  

    SET NOCOUNT OFF

The problem is that it fails and throws the exception. The result is not an Int. As additional logging showed, the result might actually contains a list of objects which is not relevant to the query!

But what is worse that sometimes it contains Int, but it is not an Id of the website I queried. It is just an Id of a different website. But it is still an Int and cast works perfectly, but I recieve wrong data!

My thought is that somewhere in internals resultsets just became mixed together. One query receives resultset of another query. It happens only at peak time, but not only in this case. I found at least 2 other place where such exception occur.

I believe turning off of a pooling connection will eliminate the problem, but I can't do this on the live enviroment. And checking HasRows on DataReader wouldn't help too since reader actually has data.

I've read here that the problem is still exists in .NET 3.5. It is surprising that such a critical problem exists in the core of he ADO.NET all this time. Is there any comments of the developers of ADO.NET about the problem?

P.S. I use .NET 2.0 + MS SQL 2005

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 17 Mar 2009 21:28:10 Z

Ishitori, could you apply http://support.microsoft.com/kb/944100 and see if it resolves your problem?
This fix looks closely related to your problem.

In addition, this is fixed in .NET 2.0 SP2 and later drops of .NET.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 18 Mar 2009 07:33:21 Z

Thanks for the answer, Matt. I've read the article and seems it's telling about losing the data, but not about mixing it in different SqlCommands as it happens in my case. Moreover, I've just checked my live environment settings and found out that SP 2 for .NET 2.0 was already applied. So, I believe it is not the reason.

For now I have scheduled pool recycling on the web server, but it is a temporary solution and will affect our customers badly. Any other ideas?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 18 Mar 2009 17:33:09 Z

Our pooling code is very complex, it has to pool and also be aware of System.Transaction state of threads, manage this with connections to SQL Server, perform transaction escalation when needed, etc... Also, inside the ASP.NET environment it is very difficult to pool things because ASP.NET uses thread aborts to cancel operations. You can have a thread in the middle of fetching a resultset from a connection and ASP.NET decides to abort the thread mid-operation. We added code in .NET 2.0 SP2 (hotfix I described) to attempt to get things back into a clean state when this happens.

Along with this you can have code that is not thread safe using connections after they are disposed for example, this can cause side effects like phantom results.

In general we have not seen a clear cut series of customers reporting phantom results. When we debug the few of these we've seen in the end it usually turns out to be an application issue like multiple threads using same connection, store procs with conditional logic, etc...

But in any case, I would like to help you get to the bottom of this, whether it's in our code or your code.

First thing you can do for me is partition the pool instead of turning it off. For example, you can partition your connection strings in different parts of your application to isolate the source of the problem. This help us isolate the repro code more precisely.

To partition, append a unique keyword to end of your connectiuon strings, for example:

using (SqlConnection conn = new SqlConnection(connectionString + ";Application Name=FunctionName;"))
{
...
}

You don't need to necessarily use a FunctionName level of isolate, you can use for example FileName for example to scope the issue to file scope.

Do this to see which particular files are having the problems, this will help isolate the code to reproduce the problem. You can then further partition down to function level to isolate which functions are having the impact.

Like the previous poster's mentioned, be sure to clearly deal with SqlConnection, SqlDataReader objects and call Close on them in method scope when possible. Avoid SqlConnection objects that exceed method scope. Avoid storing SqlConnection objects as class members, etc... The general rule is open as late as possible and close as soon as possible, this is most beneficial to pooling performance.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 19 Mar 2009 08:03:21 Z

I thought it won't be easy from the beginning :)

Thanks for the good advice, Matt. I think I will do as you suggest and add this "tracking" part to my sql connection's string. But in such a large system as mine it might take time to create appropriate architecture to cover all cases. But nevertheless it is a solution and I hope it will provide some insight.

I will post results of the experiment here when I finish. Thanks again.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 30 Apr 2009 16:47:50 Z

Well, I was reading this whole thread, and I must say that I may have found a solution. My case is not different from anyone's problems. I was occasionally getting a wrong resultset for a well known procedure. That was happening for the last 2 months and came without any warning.

I read about the possible connection pooling bug and tried to find an answer for this. I then thought If it would be of any help, to clean up the connection right after the .Open method. So I changed my production server's connection strings to add the key/value pair ";Connection Reset=true" .

The InvalidOperationException was happening more than 10 times a minute. And I might say, it had not happened for the last 1 hour, since I changed the connection strings. And I ought add, that these exceptions did not vanish right away. It took some minutes so it could be completely stopped.

I will continue to monitor this issue and see if the actual results are being affected by any situation and, in fact, have not been fixed.

Thanks anyone for the tips.

Marcos de Barros Alcantara
Flytour Turismo - Brasil

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 04 May 2009 01:12:14 Z

Marcos, let me know what version of .NET you are using as well as what SQL Server version, thanks!

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 04 May 2009 01:52:26 Z

Hi Matt,

Thanks for asking. I am using .NET 3.5 and SQL Server 2005 SP1.

As I have stated before, my results could and were wrong. The pseudo-success was due to low traffic hours. As soon as users begun using the application again, the error was evidenced.

I am again at zero-point. No ideas for this. Yet, this problem is affecting our production servers and I have no more information, so I can describe more than the others here have already done.

Do you have any ideas, Matt?

Thanks!

Marcos de Barros Alcantara
Flytour Turismo - Brasil

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 28 May 2009 15:00:40 Z

Hi Matt, Any progress on this? I'm experiencing the same problem.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 29 May 2009 06:16:03 Z

Anyone found a solution to this ? Is this a bug in MS connection pool code ?

We have disabled connection pooling and are doing ALOT of hard database connects at the moment, but the problem went away after that, but this can only be a temp. solution.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 29 May 2009 16:20:22 Z

Our pooling code is very complex, it has to pool and also be aware of System.Transaction state of threads, manage this with connections to SQL Server, perform transaction escalation when needed, etc... Also, inside the ASP.NET environment it is very difficult to pool things because ASP.NET uses thread aborts to cancel operations. You can have a thread in the middle of fetching a resultset from a connection and ASP.NET decides to abort the thread mid-operation. We added code in .NET 2.0 SP2 (hotfix I described) to attempt to get things back into a clean state when this happens.

Along with this you can have code that is not thread safe using connections after they are disposed for example, this can cause side effects like phantom results.

In general we have not seen a clear cut series of customers reporting phantom results. When we debug the few of these we've seen in the end it usually turns out to be an application issue like multiple threads using same connection, store procs with conditional logic, etc...

But in any case, I would like to help you get to the bottom of this, whether it's in our code or your code.

First thing you can do for me is partition the pool instead of turning it off. For example, you can partition your connection strings in different parts of your application to isolate the source of the problem. This help us isolate the repro code more precisely.

To partition, append a unique keyword to end of your connectiuon strings, for example:

using (SqlConnection conn = new SqlConnection(connectionString + ";Application Name=FunctionName;"))
{
...
}

You don't need to necessarily use a FunctionName level of isolate, you can use for example FileName for example to scope the issue to file scope.

Do this to see which particular files are having the problems, this will help isolate the code to reproduce the problem. You can then further partition down to function level to isolate which functions are having the impact.

Like the previous poster's mentioned, be sure to clearly deal with SqlConnection, SqlDataReader objects and call Close on them in method scope when possible. Avoid SqlConnection objects that exceed method scope. Avoid storing SqlConnection objects as class members, etc... The general rule is open as late as possible and close as soon as possible, this is most beneficial to pooling performance.

Matt, i'm testing your sample:

using (SqlConnection conn = new SqlConnection(connectionString + ";Application Name=FunctionName;"))
{
...
}

In the application where i'm working on there are places where i'm using sqlconnection directly (And I have errors here) and in the rest of the application i'm using EntLib datablock (and I have errors too in some places). We used to use Entlib 2.0 data block and we made the shift to 4.0 about half a year. I had analysed EntLib code and in entlib 2.0 a connection was always create while in version 4.0 it seems that they only have a connection per connectionString. This pair connectionString, Connection is stored in a dictionary so it can exists only one.

Using sql sonnection and using Data block i'm have errors using both ways.

I'm my case, one of the places where i have problems, I have a stored proc that do a simple select and returns only one row with 3 column. Most of the time the result is ok but sometimes only one row with one column is return. The interesting part is that this row with one column should be returned in another part of the code. Clearly the result were switch.

I've tested with a windows server 2003 and a windows XP Pro both with .net 3.5 with service packs. The sql server 2005 is version 9.00.3239.00, SP2, Enterprise Edition (64-bit).I don't have a clue on how to solve this problem.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 04 Jun 2009 17:27:53 Z

We experienced this issue too. Our app has very detailed logging. It's evident that SqlDataReader reads a result set left over from the previous query executed on the same connection a couple of seconds ago. It's an Asp.Net HttpHandler. And yes, the SqlConnection object was static. I discovered that a few days ago and figured that could be causing the problem. I fixed that and redeployed the app. The problem has not reoccurred so far, but I'd say we need to wait for at least a month to be sure it's gone. I wish we had load/stress testing scripts.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 08 Jun 2009 18:23:33 Z

Hi bbzippo,

When you say your SqlConnection object was static, what do you exactly mean by that? And what did you modify to fix this bug?? Can you please post a code example of before and after scenarios???

Thanks,
Marzban

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 08 Jun 2009 22:30:48 Z

Hi Marzban,

Before 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();
   }
 }

}

After scenario:

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.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 09 Jun 2009 16:38:24 Z

As a strict rule of thumb if you are storing a SqlConnection object as a member of any class you have already failed. Welcome to fail.org. :) Ahh, I'm jest keedding!

The simplest programming rule to remember for SqlConnection is to use a method local SqlConnection always and always be sure to close it in finally block or via a using statement:

using (SqlConnection conn = new SqlConnection(...))
{

} // <- this takes care of close for you...

-or-

try
{
 SqlConnection conn = new SqlConnection(...);
 conn.Open();

}
catch (Exception ex)
{
 ProcessException(ex);
}
finally()
{
 if (null != conn) conn.Close(); // <- Return connection to pool here.
}

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 09 Jun 2009 17:21:23 Z

Thanks bbzippo...

That helps a lot... I am reassured that the changes I have made to our data access strategy are in the right direction. I was having this problem with our web services that are heavily loaded, and our company had standardized on NHibernate ORM tool for data access from SQL Server. I believe there was a problem with the way we had incorporated NHibernate sessions, which caused the error mentioned in this thread more than 2 to 3 times a day on each of the servers on our web farm. After a lot of person hours of research and trial and error into this issue with no resolution, the company decided to drop NHibernate, and I was tasked to find a suitable replacement. I researched a few open source ORM tools, and even some paid ORM tools. In the end we determined that our apps needed a very little portion of the entire feature set that the professional or open source tools offer. Needless to say, it took me about 3 weeks, but I created a basic ORM tool for internal use from scratch (I even created a MyGeneration template to automate creation of ORM classes to mirror any db schema). Initial testing shows that it works quite well. Of course, I will keep extending its feature set as we need to, but the decision to add new features will depend on absolute need and performance impacts.

Two things I have learned from my research into this error, and my experience with NHibernate...
1. Always connect as late as possible, and release connection as soon as possible. Keeping this advice in mind, the strategy I adopted in my custom ORM tool, is to connect when a query is ready to be executed, and convert the results into a disconnected DataTableReader object, and immediately terminate the connection. Then return the disconnected DataTableReader to the calling app function. This way, you donot have to hold the connection for ransom until you have processed the datareader. At first, I thought the extra step of converting to a disconnected DataTableReader would become a performance issue, but it really is not, since ORM tools generally are not designed or used for manipulating thousands of rows of data anyways.
2. I also learned that the more the feature set and flexibility of an application grows, the more it's performance shrinks. That is why any features I add to our custom ORM tool will have to be weighed against the feature's performance impact. Currently, the same amount of data access in my custom ORM tool is between 3 to 4 times faster than our previous NHibernate solution. That is very encouraging.

Will keep interested parties posted on this as we refactor more of our heavily used apps to this new ORM tool.

Thanks,

Marzban

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 10 Jun 2009 22:54:46 Z

Thanks Matt,

Yes, I've been well aware of the recommended way of using connections and I personally always stick to it. To me it is natural that the scope that executes the transaction is responsible for acquiring and disposing the connection.
However, virtually in every application that I work with, in every other home-grown (or even widely praised) "data access framework" etc. I see violations of this pattern. Developers tend to delegate connection management to another layer just to save two lines of code per data access method.
And the reason is that nobody is aware of any negative consequences. That is in turn caused by the unclear semantics of the SqlConnection constructor and by lack of documentation on connection pooling internals.
Maybe you could explain why exactly we must not reuse connection objects and what exactly happens if we do?

Also please notice that many people in this thread who reported the problem posted perfectly correct code examples. So I'm not 100% sure that the reuse of connections is what's causing it.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 11 Jun 2009 12:54:59 Z

We have the same error. Could any confirm below things?

In one IIS server, there are two web site which use different application pool.
If we use same connection string in the two web site, if the two web site will use same connection pool though they are in different application pool?

Thanks,
-Billy

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Mon, 15 Jun 2009 16:11:59 Z

Matt I was able to find the problem in my application.

In our code there was a class with a static DbCommand variable inside that is only initialized only one time. Obviously this is a error.
Here is a sample:

class XXX
static DbCommand command = null;
static CommandText = "dbo.MySP"
static int parameter1 = 1;
static Database db = null;

static XXX
{
 db = DatabaseFactory.CreateDatabase(DatabaseName);
 command = db.GetStoredProcCommand(CommandText, parameter1);
}

public int MyResult()
{
 return (int)db.ExecuteScalar(command);
}

The problem here is that every time the ExecuteScalar is executed, internally EntLib creates a connection and sets the DbCommand.Connection property with a new connection.

Now, this all might work ok if there was only one thread, but if when we use multiple threads this is a problem.

I've create a sample application that can replicate this problem and can replicate connections being switched from one command to another. The next sample is wrong, I know, but replicate the problem.

here is a sample:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Threading;

namespace ConsoleApplication6
{
 class Program
 {
 private static string ConnectionString = "XXXXXXXXXXXXXXXXXXXXXXXXXXX";
 private static SqlCommand myCommand;

 static void Main(string[] args)
 {
 for (int i = 0; i < 5; ++i)
 {
 new Thread(ExecuteMySP1).Start();
 }

 for (int i = 0; i < 20; ++i)
 {
 new Thread(ExecuteMySP2).Start();
 }
 }

 private static SqlCommand GetMySP1Command()
 {
 if (myCommand == null)
 {
 myCommand = new SqlCommand("MySP1");
 myCommand.CommandType = CommandType.StoredProcedure;

 SqlParameter parameterSectionName = new SqlParameter(@"@Param1", SqlDbType.NVarChar);
 parameterSectionName.Value = "MyString";
 myCommand.Parameters.Add(parameterSectionName);

 SqlParameter parameterApplicationId = new SqlParameter(@"@Param2", SqlDbType.Int);
 parameterApplicationId.Value = 123;
 myCommand.Parameters.Add(parameterApplicationId);

 SqlParameter parameterMediaId = new SqlParameter(@"@Param3", SqlDbType.Int);
 parameterMediaId.Value = 2;
 myCommand.Parameters.Add(parameterMediaId);
 }

 return myCommand;
 }

 private static void ExecuteMySP1()
 {
 for (; ; )
 {

 using (SqlConnection myConnection = new SqlConnection(ConnectionString))
 {
 try
 {
 SqlCommand command = GetMySP1Command();
 command.Connection = myConnection;

 // Execute the command
 myConnection.Open();

 using (SqlDataReader reader = command.ExecuteReader())
 {
 bool canRead = false;
 if ((canRead = reader.Read()) && reader.FieldCount == 3) //should return always 3 columns. else there is a problem
 {

 }
 else
 {
 Debugger.Break();
 }
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }
 }


 Thread.Sleep(10);
 }
 }

 private static void ExecuteMySP2()
 {
 for (; ; )
 {

 using (SqlConnection myConnection = new SqlConnection(ConnectionString))
 {
 try
 {
 SqlCommand myCommand = new SqlCommand("MySP2", myConnection);
 myCommand.CommandType = CommandType.StoredProcedure;

 SqlParameter parameterHash = new SqlParameter(@"@Param1", SqlDbType.Int);
 parameterHash.Value = 1;
 myCommand.Parameters.Add(parameterHash);

 // Execute the command
 myConnection.Open();

 using (SqlDataReader reader = myCommand.ExecuteReader())
 {
 bool canRead = false;
 if ((canRead = reader.Read()) && reader.FieldCount == 1) //should return always 3 columns. else there is a problem
 {

 }
 else
 {
 Debugger.Break();
 }
 }
 }
 catch (Exception ex)
 {
 Console.WriteLine(ex.Message);
 }
 }


 Thread.Sleep(5);
 }

 }
 }
}

As you can see in this sample I'm executing 2 SP.
The first should returns 3 columns and the second should return 1 column. Otherwise I will break the application.

Ignore the exceptions and focus only in this portion of code:

bool canRead = false;
if ((canRead = reader.Read()) && reader.FieldCount == 3) //should return always 3 columns. else there is a problem, Sometimes FieldCount is equal to 1, connection switched
{

}
else
{
 Debugger.Break();
}

bool canRead = false;
if ((canRead = reader.Read()) && reader.FieldCount == 1) //should return always 3 columns. else there is a problem, Sometimes FieldCount is equal to 3, connection switched
{

}
else
{
 Debugger.Break();
}

Somehow when I'm executing a command in a connection, that connection is being switched to another command.

In this case you will see that sometimes the application will hit Debugger.Break(); with 1 column instead of 3 columns and others with 3 columns instead of 1 column. Again the connections are switched.

What I was expecting, in a very simple way, was that each command every time is executed in a connection mark that connection with a ID X and when the result came back
the command with ID X will have the result from connection with ID X I imagine that is much more complicated and is resulting in connections being switched.

You can perhaps use this code in order to try find a problem in the framework that solve the problem of every body in this thread :)

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 14:03:20 Z

Dear All,

We had migrated our web application from 1.1 to 2.0. Our application was working absolutely fine in our local server. When we deployed the application on the production server on 1st of this month, we started getting the errors given below.

Object reference not set to an instance of an object.

Index was outside the bounds of the array.

Specified cast is not valid.

Unable to cast object of type 'System.Int32' to type 'System.String'.

Column 'UserID' does not belong to table Table

Could not find prepared statement with handle 1716

That too these errors would come once in every 2-3 hrs. We had to recycle our application pool. This annoyed our customers as it would log out the application.
They started complaining as its obvious with anyone when they lose their work.

We tried all the possibilities. We checked for connection leak, dbHelper component, even tried to change the session state.
Finally, last week I found this article. Got relief reading that many people are facing the same problem.

Some recommended temporary solution to turn off the connection pool. Even though this would hit the performance, we din't have any other options. We turned the connection pool off on monday, this week.

Good news is that since then we have not seen the above errors. On an average we would recycle the application pool 4-5 times a day.

Even though this solution has given us some relief, we can't compromise with the application performance. We need to find a permanent solution so that we can turn on the pool back.
Please let me know if anyone would like to help me.

Thanks & Regards,
Raghav

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 14:17:04 Z

Matt Neerincx should not have marked bbzippo's post as answer since it´s not the main problem here. It was indeed a great contribution from bbzippo, but still not the main focus.

The question remains unanswered. The SQL Server 2005 still returns wrong results once in a while and we still doesn´t have the appropriated support.

Matt, can you please invoke some more powerful helpers from your support team to help us investigate the problem?

We are beginning to have some negative feedback from our customers and that´s very annoying for us as Microsoft certified partners.

Thanks and best regards,

Marcos de Barros Alcantara
Flytour Turismo / Envision Tecnologia
São Paulo - Brazil

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 15:16:01 Z

Microsoft really needs to look into this ugly bug. Obviously the only resolution found so far is to disable connection pooling, which we have also done, but this is not a solution.

We never experineced this on our windows 2003, sql server 2005 servers. We moved to windows 2008 64bit and sql server 2008 and all ____ broke loose. Excatly the same problems like everyone else are experincing.

We have even talked to microsoft support, but the only solution they could come up with, was to do memory dumps on our production platform when we had the problems, which we are not interested in, as customers was beginning to get very very upset, so when the temp bugfix with disabling connection pooling was mentioned we inplemented that. We cannot afford to present customers with recordsets from other customers, recycling asp.net pool etc etc, for them to have memorydumps.

Is this a 64bit problem ?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 15:26:28 Z

Marcos I've post a sample yesterdey here that show that it's possible to switch results ramdomly, of course I think this is a error. Results should never be switched.
I really hope matt or someone at MS try the sample and I hope they find the problem of switched results...

In my case took me 7 days to find my problem in a huge application I work on.

It's impressive that the first post was inserted in 2005 and no solution :(

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 15:28:57 Z

Hi Oliver,

Thanks for mention the version you are having these symptons.

In my case it still is a Windows Server 2003 and Sql Server 2005.

With your post, it just seems to me that the problem persists in the newer version of Sql Server and that is not really a good sign of progress from MS.

Microsoft support have done some memory dumps in our production server too and I don´t see where that info can get them.

I don´t see a light at the end of the tunnel. We are in the dark here.

Hope we can get some attention from the senior support guys.

Thanks for sharing!

Marcos de Barros Alcantara
Flytour Turismo / Envision Tecnologia
São Paulo - Brazil

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 17:21:08 Z

I sure wish there was a smoking gun here but we have millions of customers using SqlClient and ASP.NET without hitting these exceptions. I suspect if there was a fundamental flaw in pooling or SqlClient we would see a much higher prevalence of this issue with our customers.

In any case I am happy to help debug if you have this issue to ensure we are not missing anything. I think the quickest way to debug this would be to open a support case and get them to help debug the web server, I can be called in to assist support, they know me. If anyone can put together a reproducible scenario for this that would be best of course.

Note when moving to newer HW like 64-bit did you also increase the number of CPUs? Increasing numbers of CPUs will aggrevate existing threading bugs.

This morning I spent an hour reviewing internal customer support cases I have seen the following root causes:

1. Customer just mistakely left out field names from select statement.
2. Customer calling stored procedure with conditional logic (sometimes would select one statement, sometimes another).
3. Customer using non-method scoped SqlConnections (SqlConnection stored in member variable for example), hence multi-threaded access can occur.
4. Customer using older version of DAAB Enterprise Library that mistakely uses static SqlConnection (and thus is not thread safe).

Note from reviewing the case logs I have seen this issue reported to our support teams over and over for years and in every single case our support team worked very hard with the customer to debug the root cause, gathering crash dumps, etc... and in every case thus far it has been a customer coding issue.

Note it appears older versions of DAAB had some problems so if you are using DAAB be sure to download latest version.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 17:45:05 Z

Also, read back in the history of this posting, we have already seen many of the customers hit this because of using static SqlConnection/SqlCommand objects. Hence I would not say this long running issue was in vain, customers are solving problems using this post.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 18:03:28 Z

Matt,

Thanks for the time to answer these last replies.

I'm glad people are finding this post useful. I'm also sure your search into internal customer support really told you something. A lot of people are making those mentioned mistakes and through this post they are being able to correct their applications.

Glad, also, because this issue is happening only to a small group of customers.

Although, our case is not being listed. It seems to be a more grotesque problem.

Everyone who has this problem has mentioned the following:

1. Their connection's object is within scope.
2. They are opening and closing inside the same method and inside a try/catch block.
3. There is not any conditional elements regarding to the procedures.
4. The same code block executes hundreds of times without any problem.
5. The same code block that executes fine for hundreds of times, then returns a wrong resultset and the reader seems corrupted.
6. Some people turned their connection pooling feature off and saw the problem vanish.

Reading this post, one must be careful not to confuse himself. There is a lot of related problems, but 90% didn't catch the real one.

For instance, that's all I've got.

Thanks Matt, for your time. I know this problem is no piece of cake, just by seeing this post's timeline. (4 years).

If you can think in anything to help us debug and investigate more deeply, it's going to be of greater help.

Best regards,

Marcos de Barros Alcantara
Flytour Turismo / Envision Tecnologia
São Paulo - Brazil

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 19:11:57 Z

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;
                }
            }
        }
    }
}

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 20:34:17 Z

Thank you Matt for sharing this. We have tried many many things and have had more experienced developers and MS support team to look at our code to no help.

We DO use DAAB Enterprise library, and the latest version 4.1 from oct. 2008, and the latest framework version 3.5. Using ent lib we thought would help us from hitting erros like this as long as we encaptulate our readers into

while (DBReader.Read) and disposing and closing everything immidiatly after usage.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 16 Jun 2009 22:49:13 Z

The thing to remember is the connection pool is a process wide pool and the key to the pool is your connection string.

Other code in the same process could potentially do bad things with pooled connections (multi-threaded use) and break otherwise perfectly good code in other parts of the process.

You can partition your pool usage by making your connection string unique in some fashion, for example using the Application Name=X trick described above, this will ensure your code has it's own unique pool, this will at least help you isolate the issue down to the offending code.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 17 Jun 2009 12:19:37 Z

Matt, thank you very much, this explains a lot. But I am still curious how exactly the pool gets corrupted when we reuse connections? Is this purely a concurrency issue or something else?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 17 Jun 2009 13:19:04 Z

Matt, another question. You wrote "the connection pool is a process wide pool". I thought it was appdomain wide. For Asp.Net it makes a big difference. Could you please confirm?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 17 Jun 2009 16:42:20 Z

Yes you are correct it is app domain wide and not process wide.
Hence each app domain will be isolated from other app domain pools.

That brings up a good point you can also use app domain isolation to partition application code to determine which code is corrupting the pool as well, I did not think of that one.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 17 Jun 2009 16:59:17 Z

In response to:

Matt, thank you very much, this explains a lot. But I am still curious how exactly the pool gets corrupted when we reuse connections? Is this purely a concurrency issue or something else?

Here is what I see happening (from looking at network traces etc..) from my repro:

Suppose you have 2 threads using a single SqlConnection over multiple threads concurrently. What can happen is thread1 is in the process of sending some query, it is deep into our stack at point where it is about to write the select statement to server. Meanwhile thread2 closes connection, sending it back into pool. When we close the connection we flush state, but there is no pending state. Connection starts to return back to pool and thread1 sends command. Pooled connection immediate gets handed out to a new caller on thread3, this caller now has a pending response waiting from thread1. If thread3 sends a request and then calls SqlDataReader.read they get thread1's response.

Note the interesting part here is IF you enable MultipleActiveResultsets (MARS) this behavior actually would be avoided since with MARs enabled each request has a special ID so each thread will get only the response they sent. However enabling MARS is not a good solution to patch a threading bug in your code because with MARs you could have thread1 rolling back transaction for thread2 silently (for once example). I don't recommend using MARs to patch this one, it has it's own set of dangers the real fix is locate threads that are using SqlConnections concurrently and fix this code.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 17 Jun 2009 18:31:53 Z

Matt, thank you again. Your explanation really helps, I appreciate it.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 18 Jun 2009 09:22:01 Z

Matt, thanks on your explanation on how sql connections and pool are used, it's very usefull also I want to ask if you know a good article that explains it in more detail...

just to everybody here I did use matt trick Application Name=X to try isolate my problem, I was debugging my app during 5 days and no clue on what could cause the problem, until I found this thread. After try matt trick it took me 1 day to find my problem and solve it.

Also because our code is separated in modules and each module that uses sql could potencially have it's own DB and it's own connection string, so it was very easy to apply matt trick :).
Where I'm working on It's a huge app and since the code was breaking in too many places it took me a while to find the problem in a static sqlCommand, also took me a while because I had to see what EntLib was doing on create connections and using commands etc...etc...

Again thanks

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 18 Jun 2009 09:34:44 Z

Pedro, did you find anything specific within the newest 4.1 ent lib which caused this problem ? We use ent lib 4.1 and experience the same thing.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Tue, 23 Jun 2009 18:26:37 Z

No, our problem was not in entlib was in our code.

We declared a static dbcommand and each time we did Database.ExecuteScalar(command) a new connection was created. now in single thread this might not have been a problem but in multi-thread scenary this is a problem...

And to prove that I had previouly posted a sample where I use static command in mulit-thread and I was able to switch results across connections...

just follow this provided by matt

using (SqlConnection conn = new SqlConnection(...))
{

} // <- this takes care of close for you...

-or-

try
{
 SqlConnection conn = new SqlConnection(...);
 conn.Open();

}
catch (Exception ex)
{
 ProcessException(ex);
}
finally()
{
 if (null != conn) conn.Close(); // <- Return connection to pool here.
}

and you will have no problems...

I imagine it's hard to find the problem it took me 7 days just to see that stupid static line :P

my recomendations is to check all code for static variables and see if you find the problem.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Sat, 27 Jun 2009 08:04:15 Z

Hi, I think my message should not have been marked as Answer. The forum now moves messages marked as Answer to the top of the thread and my message now appears out of context. I wasn't replying to the original poster, I was just clarifying my prior message. Moderators, please unmark it, if possible. It will make the tread easier to follow. Thanks.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Thu, 27 Aug 2009 23:49:52 Z

Hello folks,

If you are using Entlib I recently uncovered a potential problem (not yet verified) that could trigger this IndexOutOfRangeException.

If you examine Entlib code you will see a TransactionScopeConnections class internally. It is activated when you execute something in Entlib inside a TransactionScope.

TransactionScopeConnections class attaches an event handler to the ambient transaction and this event handler fires when the transaction completes (either successfully or failing).

When transaction aborts on server side, this event can be triggered asychronously to client while the primary thread of execution in client code is still running (blocked waiting for response).

The event closes the connection, this can happen on 2nd thread while primary thread is in the middle of using the connection, corrupting the connection state.
Corrupted connection is placed back in pool and can be reused by another thread and trigger the IndexOutOfRangeException.

Note however I am still debugging this the evidence is tentative may not be 100% accurate, just a heads up.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 28 Aug 2009 06:18:10 Z

Hi Matt,

This sounds very interesting. Thank you very much for keeping us posted on your findings regarding this problem.

You previously wrote that you work within MS consulting. How would we be able to get in contact with you ?

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 28 Aug 2009 23:40:42 Z

I'm a developer for SQL Server team, not MS consulting. You can contact me directly at mattn@microsoft.com, tx.

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Wed, 23 Sep 2009 05:34:17 Z

Hi,
I have problems only when my users start generating reports (Reporting Services, 2005 Standard). Is there a way to check if there are problems with this as in the post above regarding the EntLib?
Please note that we use DotNetNuke as application framework and it does not have any problems but my app. developed over it has.

Catalin

SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

Fri, 25 Sep 2009 20:23:20 Z

The key point I make is your code can easily be the victim of other code in the same process. The EntLib issue I have found is a viable issue if you are using Entlib and TransactionScope and you are using SQL 2000 or earlier, so it's a narrow use case. I had one customer I was working with where we removed EntLib in this case and their problems went away. But given the specific scenario I have not found a way to reproduce it here at MSFT, so I cannot completely blame EntLib without clear evidence. Also, I'm working with EntLib team to ensure this is addressed in future versions.