SqlDataReader is drops columns randomly!

I have a very strange issue, I'm using System.Data.SqlClient. to get data from a SQL Server though a stored procedure. Wh开发者_JAVA百科en I test the Application at the Development and Stagging machines it works fine but when I deploy the application on the Production Server I randomly getting an SqlDataReader IndexOutOfRangeException with different column names!.

The error appears in 2 requests in each 1000 request (approximative).

The SQL Server is Clustered

Source Code:

public static List<CountryInfo> GetAllCountries(){
            List<CountryInfo> Items = new List<CountryInfo>();
            try{
                using (rdr = SqlHelper.ExecuteReader(Globals.ConnectionString, "unv_spGetAllCountries"))
                {
                    while (rdr.Read())
                    {
                        CountryInfo item = new CountryInfo();
                        item.CountryId = Convert.ToInt32(rdr["CountryId"]);
                        item.CountryName = rdr["CountryName"].ToString();
                        item.FirstLevel = rdr["FirstLevel"].ToString();
                        item.SecondLevel = rdr["SecondLevel"].ToString();

                        Items.Add(item);

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            Items.TrimExcess();
            return Items;
        }

Stored Procedure:

select * from unv_tblCountries order by CountryName;

Already Tested

• Check Stored Procedure column names.
• Check Reader Column names.
• Check connection String.

Anyone faced like this issue and solve it?

I'll wager this is not a data-reader issue. My guess would be that one or more user accounts is using a more-specific (and older) copy of the sproc (etc) - for example Fred.MyProc instead of dbo.MyProc, or there is conditional branching logic in the sproc that returns different columns in some cases - maybe a branch of code you forgot to update.

Another potential issue is perhaps different case-sensitivity in the DB causin different objects to be used; i.e. Myproc vs MyProc - which can be different if the DB is case-sensitive.

To find out for sure, attach a SQL trace to log exactly what (and by whom) is sent for the failing cases; then repro that in something like SSMS, comparing dev to prod.

I found it the SqlDataReader varibale rdr was declared as static in the controllers base class which maked it a shared varibale between all the controllers. The requests threads were using the same DataReader and changing the columns in it.