none
SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange

    Question

  • 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:
    1. We deal with SQLClient, not with JDBC.
    2. 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
    Wednesday, November 16, 2005 2:02 PM

Answers

  • Just as an experiment I wrote a quick multi-threaded app to replicate the problem with static SqlConnections.
    Very quickly I am able to generate the target exception on my quad proc 64-bit machine.

    The key find from this experiment is that your specific code in one place may be safe but other code in the same process that is not safe can corrupt the state of SqlConnections in the pool.  If you run the code sample you will note that "safe" threads are getting the IndexOutOfRangeExceptions.

    Hence a probable cause of these issues is some other code in your process that is improperly using pooled connections over multiple threads.

    A sugggestion I made earlier to solve this issue (or at least isolate it) thus still holds:  Add Application Name=X to your connection strings to "partition" your pools.  You can use any value you want for X, you can use the current thread's ID for example to partition pools by thread id, you can use filename, you can use function name, etc...  Once you partition then you can isolate (divide and conquer) which remaining parts of the code are corrupting the pool and the rest of your code that uses other pool partitions will be 100% safe from the bad code.

    You will note from the code example below, only one thread in 5 will share the global connection globalConn.  Each thread runs a different select statement based on the thread id and then attempts to read the field name expected.  You will need to modify the connection string to point to your SQL Server but the sample does not rely on any pre-created tables to run (it just selects expression value).


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading;
    using System.Data.SqlClient;
    
    namespace PoolCorrupt
    {
        class Program
        {
            static void Main(string[] args)
            {
                int threadCount = 100;
                for (int i = 1; i <= threadCount; i++)
                {
                    new Thread(new ThreadStart(Program.WorkerThread)).Start();
                }
            }
            static int tidCounter = 0;
            static UInt32 value = 0;
            static SqlConnection globalConn = null;
            static SqlConnection GetConn(string connect, bool useGlobalConn)
            {
                SqlConnection connLocal = null;
                if (useGlobalConn)
                {
                    connLocal = globalConn;
                    if (null == connLocal)
                    {
                        connLocal = new SqlConnection(connect);
                        connLocal.Open();
                        globalConn = connLocal;
                        return connLocal;
                    }
                    else
                    {
                        return connLocal;
                    }
                }
                else
                {
                    connLocal = new SqlConnection(connect);
                    connLocal.Open();
                    return connLocal;
                }
                
            }
            static object consoleLock = new object();
            static void WorkerThread()
            {
                int tid = Interlocked.Increment(ref tidCounter);
                string select = string.Format("select {0} as [f{1}]", tid, tid);
                string fieldName = string.Format("f{0}", tid);
                string connect = "server=MySqlServer;user id=test;password=test;";
                int readCount = 0;
                bool useGlobalConnThread = (0 == (tid % 5));
                SqlConnection conn = null;
                SqlCommand cmd = null;
                SqlDataReader dr = null;
                for (;;)
                {
                    try
                    {
                        conn = GetConn(connect, useGlobalConnThread);
                        cmd = conn.CreateCommand();
                        cmd.CommandText = select;
                        cmd.CommandTimeout = 5;
                        dr = cmd.ExecuteReader();
                        dr.Read();
                        int fieldValue = (int)dr[fieldName];
                        System.Diagnostics.Debug.Assert(fieldValue == tid);
                        readCount++;
                        if (0 == (readCount % 2500))
                        {
                            Console.WriteLine("Thread{0} reads{1}", tid, readCount);
                        }
                    }
                    catch (IndexOutOfRangeException iorEx)
                    {
                        lock (consoleLock)
                        {
                            Console.WriteLine("Caught targeted exception IndexOutOfRangeException:");
                            Console.WriteLine("expected -> {0}", fieldName);
                            Console.WriteLine("actual   -> {0}", dr.GetName(0));
                            if (useGlobalConnThread)
                            {
                                Console.WriteLine("Ok, I'm a bad thread and I'm sharing SqlConnections, I know this is going to happen.");
                            }
                            else
                            {
                                Console.WriteLine("Hey, I'm a safe thread and just a victim!");
                                System.Diagnostics.Debug.Assert(false);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        lock (consoleLock)
                        {
                            Console.WriteLine(ex.Message);
    
                            if (useGlobalConnThread)
                            {
                                Console.WriteLine("Ok, I'm a bad thread and I'm sharing SqlConnections, I know this is going to happen.");
                            }
                            else
                            {
                                Console.WriteLine("Hey, I'm a safe thread and just a victim!");
                            }
    
                            // Reset globalConn on failure.
                            SqlConnection connLocal = GetConn(connect, false);
                            SqlConnection connToClose = globalConn;
                            globalConn = connLocal;
                            try { connToClose.Close(); }
                            catch (Exception) { };
                        }
                    }
                    finally
                    {
                        if (!useGlobalConnThread)
                        {
                            if (null != conn) conn.Close();
                        }
                        if (null != cmd) cmd.Dispose();
                        if (null != dr) dr.Close();
                        conn = null;
                        cmd = null;
                        dr = null;
                    }
                }
            }
        }
    }
    
    Tuesday, June 16, 2009 7:11 PM
  • 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.
    Monday, June 08, 2009 10:30 PM

All replies

  • Yinon,

    I have the same suspension as yours that the sp doesn't return the field "RUSureYouWantThisField" field on some cases (either on special value of input parameter or db/table issue). Could you try calling myReader.GetSchemaTable() and logging the schema before reading the field? This may help you identifying the issue.

    Thanks,
    Kevin
      
    Friday, December 09, 2005 11:25 PM
  • Hi ,

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

    thanks  in advance
    Bali Reddy

    Thursday, December 29, 2005 11:19 PM
  • 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

     

     

    Thursday, June 29, 2006 12:39 PM
  • 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.
    Thursday, June 29, 2006 3:47 PM
  • 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

     

     

    Friday, June 30, 2006 2:29 PM
  • 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?

    Friday, June 30, 2006 4:34 PM
  • Hi Alazel
    Monday, July 03, 2006 11:06 AM
  • 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

     

    Monday, July 03, 2006 11:11 AM
  • 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.
    Monday, July 03, 2006 8:18 PM
  • 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

    Tuesday, July 04, 2006 5:29 AM
  •  

    You should create a function to return a datareader, and when using, bebore calling the sequence

    while (datareader.Read())

    {

    }

    you should check as the following code

     if (!datareader.HasRows) return;

    *************************************************

    I also create a function that call store procedures in SQL, but I write in VB.NET

    You can change it to C# easily, by replacing this module in to class

     

    Imports System.Data.SqlClient

    Module ConnectSql

    'name of the database server 

    Public Server As String  'name

    'database name

    Public Database As String

    Public UserID As String

    ' connection object

    Public con As SqlConnection

    #Region "Manage the connection existed or not"

    Public Function OpenSqL() As SqlConnection

    If Exist() = True Then

    Return con

    End If

    Try

    Dim conString As String

    conString = "data source=" & Server & ";initial catalog=" & Database & ";persist security info=False;user id=" & UserID & ";pwd=" & Password

    con = New SqlConnection(conString)

    con.Open()

    Catch ex As Exception

    con = Nothing

    End Try

    Return con

    End Function

    Public Sub CloseSql()

    Try

    If con.State <> ConnectionState.Closed Then

    con.Close()

    End If

    Catch ex As Exception

    Throw ex

    End Try

    con = Nothing

    End Sub

    Private Function Exist() As Boolean

    If con Is Nothing Then

    Return False

    Else

    Return True

    End If

    End Function

    #End Region

    ' this function is used to call Store procedures

    ' after calling, it return a datareader

    'optional paramenters ( parameters of the store procedure called)

    'optional value values of each parameter

     

    Public Function SP_Select(ByVal sp_name As String, Optional ByVal params() As String = Nothing, Optional ByVal values() As Object = Nothing) As SqlDataReader

    Dim dr As SqlDataReader = Nothing

    Try

    Dim command As SqlCommand = New SqlCommand(sp_name, OpenSqL())

    ' set type of command

    command.CommandType = CommandType.StoredProcedure

    ' check if the parameters are passed into the function 

    If (Not params Is Nothing) Then

    Dim len As Integer = params.Length - 1

    Dim i As Integer

    For i = 0 To len

    command.Parameters.Add(New SqlParameter(params(i), values(i)))

    Next

    End If

    dr = command.ExecuteReader()

    Catch ex As Exception

    MessageBox.Show(ex.ToString)

    dr = Nothing

    Throw ex

    End Try

    Return dr

    End Function

     End Module

     

    Tuesday, July 04, 2006 7:59 AM
  • 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.


    Tuesday, July 18, 2006 1:01 AM
  • 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.

    Wednesday, July 19, 2006 7:52 PM
  • 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?

     

    Tuesday, August 01, 2006 3:56 PM
  •  
    Thursday, August 17, 2006 2:14 AM
  • 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);
    }
    Thursday, August 17, 2006 2:37 AM
  • 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?
    Wednesday, September 13, 2006 3:45 PM
  • 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.

    Thursday, September 14, 2006 4:02 PM
  • 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.
    Friday, September 15, 2006 2:15 AM
  • 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?
    Friday, September 15, 2006 10:01 PM
  • 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

    Tuesday, September 19, 2006 5:17 PM
  • 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).
    Wednesday, September 20, 2006 4:58 PM
  • Do you have a ticket id (or something similar) where we can follow the progress of this problem?
    Friday, September 22, 2006 8:00 AM
  • 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

     

    Monday, September 25, 2006 4:08 PM
  • 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
    Friday, September 29, 2006 1:28 PM
  • I am experiencing the same problem when the server is heavily loaded.

    ASP.NET 2.0, MS SQL Server 2000

    Thursday, October 05, 2006 12:52 PM
  • 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

    Thursday, October 05, 2006 3:27 PM
  • 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.
    Thursday, October 05, 2006 6:22 PM
  • 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

     

    Wednesday, January 10, 2007 7:22 AM
  • 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
    Tuesday, January 16, 2007 11:34 PM
  • 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

     

     

    Saturday, January 20, 2007 9:05 AM
  • 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

    Monday, January 22, 2007 10:34 PM
  • 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.
    Monday, January 22, 2007 10:36 PM
  • 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

    Monday, February 26, 2007 10:06 AM
  • 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

    Tuesday, February 27, 2007 12:19 PM
  • 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

    Thursday, March 01, 2007 3:46 PM
  • 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

    Wednesday, March 14, 2007 8:51 PM
  • Has any solution been found on this matter, as I am experiencing the same exact problem.
    Saturday, March 31, 2007 4:35 PM
  • 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.
    Monday, April 02, 2007 4:39 PM
  •  Zyad wrote:
    Just to let you guys know that I've solved the problem.

    The problem was that my dataAccess connections and methods were defined as STATIC, which means that sometimes the datareader was receiving data from other tables.

    I changed that and everything is now ok.

     

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

     

    Thanks

    Tuesday, April 03, 2007 8:07 PM
  • 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 ?

    Thursday, April 12, 2007 2:17 PM
  • 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.

     

    Thursday, April 12, 2007 2:51 PM
  • 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.
    Thursday, April 12, 2007 3:30 PM
  • 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.

    Thursday, April 12, 2007 3:41 PM
  • 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?

     

     

    Thursday, April 12, 2007 3:43 PM
  • 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.
    Thursday, April 12, 2007 3:49 PM
  • I have turned off connection pooling.

     

    Back in a few day to post my observations.

    Thursday, April 12, 2007 6:36 PM
  • 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.
    Thursday, April 12, 2007 11:15 PM
  •  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.

    Friday, April 13, 2007 3:43 PM
  • hmm.. interesting, please give it a few more days tho just be sure.. incidentally are you using the DAAB?
    Friday, April 13, 2007 3:48 PM
  •  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.

    Friday, April 13, 2007 4:25 PM
  • 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.
    Friday, April 13, 2007 8:55 PM
  • 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.

    Saturday, April 14, 2007 5:25 AM
  • 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.  =)

    Monday, April 16, 2007 9:18 AM
  • 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.
    Wednesday, April 18, 2007 8:48 PM
  •  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.

    Thursday, April 19, 2007 5:30 AM
  • Still going.
    Tuesday, May 08, 2007 4:18 PM
  • 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

    Thursday, May 10, 2007 5:26 PM
  •  

    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 += "<br>";

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

     

        this._SitesDOM = new XmlDocument();

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

     

        // Execute Reader

        __TempDebugMessage += "ExecuteReader<br><br>";

        rs = sqlCmd.ExecuteReader();

     

        // Load DataReader Main Info

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

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

     

        // Load Schema Table

        DataTable __rsDT = rs.GetSchemaTable();

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

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

     

        // Load Columns

        __TempDebugMessage += "FIELDS:<br>";

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

        {

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

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

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

        }

        __TempDebugMessage += "<br>";

     

        int __CReadCount = 0;

        __TempDebugMessage += "ROWS:<br>";

        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<BR>";

     

          }

          catch(Exception exSP)

          {

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

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

          }

        }

                              

      }

      catch (Exception ex)

      {

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

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

      }

      finally

      {

        if(rs!=null) 

          rs.Close();

      }

     

    }

     


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

     

    GLS01 – Site_Uid

     

    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)

     

    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

    Friday, May 11, 2007 3:20 PM
  • 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 ?

     

     

    Friday, May 11, 2007 3:51 PM
  • 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)?

    Friday, May 11, 2007 6:14 PM
  •  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.

    Friday, May 11, 2007 6:39 PM
  • 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:
    1. 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]
    2. Atttempt to read the column
    3. Catch the System.IndexOutOfRangeException
    4. Trace out the exception.
    5. Trace out the properties FieldCount and HasRows of the reader object.
    6. 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

    Tuesday, May 15, 2007 3:10 PM
  •  

    No posts in over 6 months, has anyone found a resolution to this problem?
    Monday, November 19, 2007 10:03 PM
  • 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

    Tuesday, November 20, 2007 10:08 AM
  •  

    We are still running with connection pooling off without any issues.
    Tuesday, November 20, 2007 4:45 PM
  • 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.
    Tuesday, November 20, 2007 5:33 PM
  • 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

    Wednesday, November 21, 2007 9:42 AM
  • 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.

     

    Monday, November 26, 2007 8:00 PM
  • 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

     

    Tuesday, November 27, 2007 9:09 AM
  • 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 Smile 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

    P

    Tuesday, February 12, 2008 11:11 AM
  • 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.
    Wednesday, March 19, 2008 5:36 PM
  • 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.

    Wednesday, March 19, 2008 11:16 PM
  • What makes something static vs non-static?

     

    Thursday, March 20, 2008 2:17 PM
  • 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

    Thursday, March 20, 2008 4:49 PM
  • Thanks.

     

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

    Thursday, March 20, 2008 5:02 PM
  • 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.
    Thursday, March 20, 2008 5:53 PM
  •  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

    Thursday, March 20, 2008 7:25 PM
  • 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 timeWink

     

    Itai.

    Sunday, April 20, 2008 5:51 PM
  • 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?

     

     

    Friday, July 04, 2008 1:34 AM
  • 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.
    Thursday, July 10, 2008 1:52 PM
  • 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.
    Tuesday, July 29, 2008 3:02 PM
  • 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?
    Friday, January 30, 2009 7:03 PM
  • 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

    Thursday, February 19, 2009 3:48 PM
  • 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 

    Friday, February 20, 2009 10:52 AM
  • 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
    Monday, March 02, 2009 12:46 PM
  • 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

    1. It was using try, catch and finally to close the connection. i changed to "USING"
    2. 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();
    3. 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
    Saturday, March 07, 2009 9:19 PM
  • 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
    Monday, March 16, 2009 4:23 PM
  • 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.

    Tuesday, March 17, 2009 9:28 PM
  • 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?
    Wednesday, March 18, 2009 7:33 AM
  • 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.



    Wednesday, March 18, 2009 5:33 PM
  • 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.
    Thursday, March 19, 2009 8:03 AM
  • 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
    Thursday, April 30, 2009 4:47 PM
  • Marcos, let me know what version of .NET you are using as well as what SQL Server version, thanks!
    Monday, May 04, 2009 1:12 AM
  • 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

    Monday, May 04, 2009 1:52 AM
  • Hi Matt, Any progress on this? I'm experiencing the same problem.
    Thursday, May 28, 2009 3:00 PM
  • 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.
    Friday, May 29, 2009 6:16 AM
  • 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.

    Friday, May 29, 2009 4:20 PM
  • 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.
    Thursday, June 04, 2009 5:27 PM
  • 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
    Monday, June 08, 2009 6:23 PM
  • 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.
    Monday, June 08, 2009 10:30 PM
  • 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.
    }
    Tuesday, June 09, 2009 4:38 PM
  • 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
    Tuesday, June 09, 2009 5:21 PM
  • 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.
    Wednesday, June 10, 2009 10:54 PM
  • 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
    Thursday, June 11, 2009 12:54 PM
  • 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 :)

    Monday, June 15, 2009 4:11 PM
  • 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
    Tuesday, June 16, 2009 2:03 PM
  • 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

    Tuesday, June 16, 2009 2:17 PM
  • 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 ?
    Tuesday, June 16, 2009 3:16 PM
  • 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 :(
    Tuesday, June 16, 2009 3:26 PM
  • 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
    Tuesday, June 16, 2009 3:28 PM
  • 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.
    Tuesday, June 16, 2009 5:21 PM
  • 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.
    Tuesday, June 16, 2009 5:45 PM
  • 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
    Tuesday, June 16, 2009 6:03 PM
  • 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;
                    }
                }
            }
        }
    }
    
    Tuesday, June 16, 2009 7:11 PM
  • 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.

    Tuesday, June 16, 2009 8:34 PM
  • 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.
    Tuesday, June 16, 2009 10:49 PM
  • 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?
    Wednesday, June 17, 2009 12:19 PM
  • 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? 
    Wednesday, June 17, 2009 1:19 PM
  • 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.
    Wednesday, June 17, 2009 4:42 PM
  • 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.
    Wednesday, June 17, 2009 4:59 PM
  • Matt, thank you again. Your explanation really helps, I appreciate it.
    Wednesday, June 17, 2009 6:31 PM
  • 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
    Thursday, June 18, 2009 9:22 AM
  • 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.
    Thursday, June 18, 2009 9:34 AM
  • 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.
    Tuesday, June 23, 2009 6:26 PM
  • 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.
    Saturday, June 27, 2009 8:04 AM
  • 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.
    Thursday, August 27, 2009 11:49 PM
  • 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 ?
    Friday, August 28, 2009 6:18 AM
  • I'm a developer for SQL Server team, not MS consulting.   You can contact me directly at mattn@microsoft.com, tx.
    Friday, August 28, 2009 11:40 PM
  • 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
    Wednesday, September 23, 2009 5:34 AM
  • 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.
    Friday, September 25, 2009 8:23 PM
  • Hi,

    Try to use field index instead of field name.

    strRetVal =                        myReader[SAME_FIELD_NAME_ALWAYS].ToString(); 

    strval= myReader[index].ToString(); //
    strval= myReader[1].ToString();


    Monday, December 21, 2009 3:09 PM
  • My co-worker and I have been trying to deal with this situation for months now, and I was optimistic when she found this thread, but there is no resolution that we can see.  We are in charge of a .NET 3.5 application, 95% VB.NET, running 2008 R2 on our web server and SQL Server 2008 on our database server.  We get the errors described here (primarily GetOrdinal) sporadically, but on a pretty consistent basis.  We have tried switching from DataReaders to DataSets.  Our application uses a custom DataAccessLayer class almost exclusively, and within that we do use DAAB (currently version 2).  We are not using out-of-method connections.  Below is a typical example of our DAL methods:

    Public Function MyDALMethod(ByVal Param1 As Integer, ByVal Param2 As Integer) As System.Data.IDataReader

            Dim db As Database = DatabaseFactory.CreateDatabase()
            Dim sqlCommand As String = "usp_MyProcedure"
            Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
            db.AddInParameter(dbCommand, "Param1", DbType.Int32, MyParam1)
            db.AddInParameter(dbCommand, "Param2", DbType.Int32, MyParam2)

            Return db.ExecuteReader(dbCommand)
           
        End Function

    In our code we just instantiate a DAL var and call the desired method.  After using the DataReader, the referencing code will close, dispose, and set the reader to nothing.  However, nothing is done with the reference to the DAL.  I've wondered if this is part of our problem.   A typical method would use our DAL like this:

    Dim DAL as New DataAccessLayer

    Dim dbReader as system.data.idatareader

    dbreader = DAL.MyDALMethod(parm1, parm2)

    While dbreader.read

        i = dbreader.items("column1")

        j = dbreader.items("column2")

    End While

    dbreader.close()

    dbreader.dispose()

    dbreader = nothing

     

    Obviously, we're not helping ourselves by running on a dated version of DAAB either.  We've begun enumerating columns in our stored procedures instead of using "select *" on new procs/methods, but there's a large codebase that hasn't been converted. 

    Does anyone see the problem in our situation, and did any of the participants in this very long, detailed thread ever sort out your own issue?

    Thanks for any feedback you may have.

    Tuesday, September 28, 2010 7:10 PM
  • Just a follow up.  I worked with the Enterprise Library team and got this issue fixed in Enterprise Library 5.0.  If you still have this problem with 5.0, please let me know.

    Monday, October 18, 2010 3:37 PM
  • This was one of the most difficult errors to track down for me. But what fixed it was removing my static table adapters. Never got the error again after that.
    Friday, December 17, 2010 8:09 PM
  • Hello.

    I also have same problem and suffering more than months. Of course it's not a long period in comparison.

    What I am wondering is, I don't use static class, method or variable for connection. Similar pattern of code is friend and shared. My connection is from other assembly that called by "friend" class. And also function that uses connection is defined as "friend shared". Is this definition also cause of this problem?

    Tuesday, January 04, 2011 11:47 AM
  • In your case the other class is holding a SqlConnection as a member variable and you are using it from a friend class.  It's the same basic problem.


    Matt
    Tuesday, January 04, 2011 5:08 PM
  • Hi,

     

    Even I don't use any static variables for connection? SqlConnections are from other assembly and they are defined as member variable in the function. Please take a look below.

    Assembly1

    Public Class Dummy

    Public Shared GetDataFromDB(...) as Object

    Dim objDBA as new DBAccess

    blah blah

    DataSet = objDBA.ExecuteAndReturn(Query)

    blah blah

    End Function

    End Class

     

    Assembly2

    Public Class DBAccess

    private m_Connection as SqlConnection

    Public Function ExecuteAndReturn(byval strQuery as String) as DataSet

    blah blah

    If Not m_Connection is Nothing Then m_Connection = New SqlConnection()

    DataSet.Connection = m_Connection

    execute code

    Return DataSet

    End Function

    End Class

     

    Sometimes, feature related to Assembly1 make invalid column or index out of range problem.

    We sold our software over years with above code. But in the new version in development is the first one using Assembly1. So, our software did not used shared function before but member variable connection.

    Do you think above shared function cause the problem? Please advice.

    Wednesday, January 05, 2011 10:29 AM
    1. The DBAccess class has a SqlConnection member variable.
    2. In GetDataFromDB, you always create a new DBAccess class, which will have a new SqlConnection.
    3. It appears that DataSet holds a reference to the internal SqlConnection as well, and DataSet is passed out from DBAccess class
    4. So both DataSet and DBAccess have a reference to a SqlConnection
    5. Given above there should not be a problem unless you pass a reference to DataSet or DBAccess outside of GetDataFromDB.
    6. If you want to guarantee to avoid the problem, ensure GetDataFromDB does not pass out a reference to DataSet or DBAccess, ensure that GetDataFromDB closes out both DataSet and DBAccess before returning to caller.

    My guess is you are passing out DataSet or DBAccess and some other code is holding onto it and sharing it.

     


    Matt
    Wednesday, January 05, 2011 7:19 PM
  • Hi Matt,

    I spent a whole day reading this blog and analyzing my code as I am also facing the same issue.

    My app is a windows based client-sever system.

    As stated in several post above that one of the potential cause to this issue can be the use of SQLConnection as a member variable of a class.

    I also had this flaw in my code and I thought this would be the reason that's causing the problem and I was able to reproduce this issue in debug environment.

    So I did rectify the flaw and ensured that there is no global use of SQLConnection. But still to no help.

    The issue still persists. Its melting my head now. I am unable to understand why this issue occurs even after taking care of SQLConnections.

    FYI, at the moment there is only one client connected to my server in debug environment with all necessary changes applied but still the issue persists.

    Can you please help me out. Its really driving me nuts.

    Thanks..........

    Saturday, February 05, 2011 1:38 PM
  • Hi,

    I am also facing same problem. My site crashes after few days and in log I am getting below exception:

     

    Stack trace: at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)

    at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name).

    I have made checking as per suggestions mentioned in this thread. Still My site is crashing. Site is hosted on window server 2008 R2 64bit machine. and it uses SQl server 2005 32bit.

     

    Does Any one get solution for this problem ?

    Thanks....


    Jigar
    Tuesday, March 15, 2011 12:51 PM
  • So taking on board the concept that an SqlConnection should never escape from a method, and that finally/using should dispose it, has anyone done any work on an FxCop rule to locate violations?
    Wednesday, April 13, 2011 1:16 PM
  • I'm experiencing the very same problem

    .NET 4.0 SQL Server 2008 IIS 7 with very little code and rarely more than one person accessing my website at a time. That's really weird. Happens once every two days.

     

    Tuesday, April 19, 2011 7:20 PM
  • Follow up to FxCop question, it’s called DisposeObjectsBeforeLosingScope: http://msdn.microsoft.com/en-us/library/ms182289(v=VS.100).aspx


    Matt
    • Proposed as answer by ladoleste Thursday, April 28, 2011 7:17 PM
    • Unproposed as answer by ladoleste Thursday, April 28, 2011 7:17 PM
    Tuesday, April 26, 2011 6:30 PM
    • Proposed as answer by ladoleste Thursday, April 28, 2011 7:18 PM
    Thursday, April 28, 2011 7:17 PM
  • Hi folks,

    I was able to crack the nut in my application.

    And to everyone's surprise the flaw was existing in my application and there is no problem with SQLServer.

    As mentioned in several other posts above, my application was also playing with multiple threads using the same SQLConnection and as a result was causing the SQL Data Reader to go out of sync.

    I was able to figure out the problem with help of pool partitioning. I executed the pool partition of SQL Connection everywhere in my app and hence was able to pin point the problem area which was causing the SQL Data Reader to go out of sync.

    I then rectified the flaw in my app by creating a new SQL Connection for each thread and since then I haven't faced the issue.

    Many of us have identified the same thing in their apps. This is one of the primary cause for this issue. However, there can be others as well.

    Thanks,

    Parth Shah

    • Proposed as answer by parth.shah Monday, August 08, 2011 6:54 AM
    Monday, August 08, 2011 6:54 AM
  • After a couple years trying to track down this problem, visiting and posting in this thread several times, and trying everything under the sun (including making sure all connections were being closed properly, re-writing our DAL, etc.) I've finally found the fix - at least for our application. While digging around the other day I came across the "Enlist" argument for SQL connection strings. I wasn't very comfortable with the description, and it made sense that if the app was trying to automatically maintain SQL transaction state you could get crossed data in readers, datatables, etc. Our app doesn't use SQL transactions, at least not in the code. So last night I added the argument "Enlist=False" to all of our connection strings and so far we've not gotten a single GetOrdinal error all day! We've had a total of 13 errors. Normally we would've thrown 80 or more errors by now - the majority of them GetOrdinal errors. So I would suggest that anyone receiving this error, if you're not handling SQL transactions through code, try adding the "Enlist=False" argument to your connection strings and this very well may work for you as well. Mike
    • Edited by mikegar Monday, January 23, 2012 5:59 PM
    • Proposed as answer by mikegar Monday, January 23, 2012 6:35 PM
    Monday, January 23, 2012 5:57 PM
  • Coming up on 3 weeks since I made this change, and still not a single GetOrdinal error.
    Friday, February 10, 2012 6:07 PM
  • Anyone find how to fix the problem?

    We have the same problem. It began a month ago and at this moment we don't have a fix for it. It's frustraiting for us :(

    The application work great for over 10 years!!! and now we have this problem. How can it be possible?

    Can someone help us?

    Thanks

    DS

    Tuesday, April 10, 2012 8:01 AM
  • It's the same for me.

    This exception is a calamity.
    But the link up by ladoleste show good solution for me. I'll see results in a long futur... Many work in the application to verify close connection.

    Good work guys. This post is instructif.

    Frt.

    (Sorry for my bad English ^^')

    Thursday, April 12, 2012 10:10 AM