SqlDataReader is drops columns randomly!

Question

I have a very strange issue, I'm using System.Data.SqlClient. to get data from a SQL Server though a stored procedure. When 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?

You'll have to provide more details and preferably the relevant source. Without it, there's little to say about it. — Willem van Rumpt
– Willem van Rumpt, Commented Jan 16, 2011 at 10:15
I suggest a catch block that will log this exception and all passed in parameters. — Oded
– Oded, Commented Jan 16, 2011 at 10:19
select * ... is not going to help you, if unv_tblCountries changes (or is different in different instances) you'll get errors at a distance rather than within the query itself -- this makes debugging harder. Thus select * in production code is generally regarded as poor form (ad-hoc queries are different). — Richard
– Richard, Commented Jan 16, 2011 at 12:25
@Richad I added the columns to the select statement but still the issue not solved :( — Khaled Musaied
– Khaled Musaied, Commented Jan 17, 2011 at 9:55

Marc Gravell · Accepted Answer · 2011-01-16 11:26:52Z

3

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.

edited Jan 16, 2011 at 11:26

answered Jan 16, 2011 at 11:20

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Khaled Musaied Over a year ago

Thanks for your answer, I modified the connection string to connect to the live database with the same username and password and it was working fine the issue is when deploying the app on the production servers!

Marc Gravell Over a year ago

@Khaled then your prod server isn't configured the same, or there is a branch of code that is only reached with he data in prod; you will have to try to repro it and trace it, to see what.

Khaled Musaied Over a year ago

Thanks for your corporation , I have a doubt in the server and network, BTW which configuration you meant?

Khaled Musaied · Accepted Answer · 2011-01-17 12:47:00Z

1

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.

answered Jan 17, 2011 at 12:47

Khaled Musaied

2,4933 gold badges25 silver badges38 bronze badges

Collectives™ on Stack Overflow

SqlDataReader is drops columns randomly!

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related