Getting nonsensical results from SQL query

Question

I've encountered a very, very strange situation in my application. I say strange because the errors are intermitent and I cannot find out why they occur.

I've pass a sqlcommand to the DB (Sql 2005) trying to get a bigint (column ID_Facultate) from a single table. The problem is that I've got a string (the name of some lecture) that has nothing to do with the table I've tried to access.

Also, when I get these errors, all my application is going crazy.

In the SQL Profiler everything seems fine (the last executing rows seem to be the ones sent by my application)

Here are some links to 3 captured screens during the errors and the code I've used. Please note the Watch1 with the very wrong string value where I expected some integer

Updates:

I will consider using both the name of the column and/or ExecuteScalar.

Still, the problem is, as seen in the Watch1 that the query returns a value that has nothing to do with that table.

The code was expecting an integer (14) while the DB returned a string... (http://www.unitbv.ro/cata/errors/2.jpg) The same in http://www.unitbv.ro/cata/errors/3.jpg : I was expecting a value from 1 to 20 while the DB/layer seems to return the value 305 which is not in that table...

In the following post (various forums) I asked the same or related questions (and no positive answer yet)

At a high level the code looks OK, are you doing anything odd or non-standard in your database or in the connection string? — Jesse Taber
– Jesse Taber, Commented Jun 6, 2009 at 12:18
Try using SQL Profiler and running a database trace to see what queries are being called and the data being returned. It might help? — Kane
– Kane, Commented Jan 29, 2011 at 4:40
+1 to Kane's comment - you need a breakpoint on your sql query and then take that query and watch it in profiler and check what is coming back from it. — Lance McNearney
– Lance McNearney, Commented Jan 29, 2011 at 4:40
The query in the Profiler is the same that is sent by the .net code. The result are, sometimes, strange - they are not from the table the code queried but from other tables... and this is very strange to me... — Catalin
– Catalin, Commented Jan 29, 2011 at 4:40

Nelson Reis · Accepted Answer · 2009-06-06 09:04:59Z

1

From your code I could only think of getting the value using the field name.

rd["ID_Facultate"].ToString()

Although your code seems correct, I always prefer using field names to get the values instead of index position, that can be easily changed in a number of situations. Some of them, maybe like this one, you can't even figure it out.

answered Jun 6, 2009 at 9:04

Nelson Reis

4,8109 gold badges46 silver badges61 bronze badges

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

Comments

Kane · Accepted Answer · 2009-06-06 08:45:41Z

0

Can you think of why the idr.FieldCount has a value of 4 in screens 1 and 2 but only 3 in screen 3. Could the problem be resolved by using the column name instead of the ordinal position?

id_faculate = long.Parse(idr["ColumnName"].ToString());

edited Jun 6, 2009 at 8:45

answered Jun 6, 2009 at 8:29

Kane

16.8k11 gold badges67 silver badges95 bronze badges

Comments

Julien Poulin · Accepted Answer · 2009-06-06 09:35:24Z

0

Since you're only getting 1 value back, you should consider using a SqlCommand.ExecuteScalar instead of getting a DataReader.

answered Jun 6, 2009 at 9:35

Julien Poulin

13k10 gold badges53 silver badges77 bronze badges

Comments

Jon Adams · Accepted Answer · 2009-07-16 13:16:22Z

0

From everything you've told us, nothing seems wrong. It's probably something else for which you didn't give us the have info.

Some other things to try:

Run DBCC CHECKDB
Ensuring the query returns the correct result in SQL Server Management Studio
Try the database on another machine/SQL instance and see if the same problems persist

answered Jul 16, 2009 at 13:16

Jon Adams

25.3k18 gold badges85 silver badges122 bronze badges

Comments

The Dag · Accepted Answer · 2014-05-05 14:05:03Z

0

This may be a blind avenue... but is your context a web application? Are you using an old EntLib version (4 or lower)?

There is a known bug in EntLib 4 (and earlier) versions that can strike under heavy load in the context of an ASP.NET app hosted on IIS. I don't remember all the details, but basically IIS has an optimization where, under heavy load, it might "park" a thread during the wait (to free it up so it can be used for something else), and then try to continue running the same LOGICAL thread on what is in fact another thread when the I/O wait is over.

If you have lots of data access going on this can produce the symptoms you are seeing - the results from one query ends up being received in a thread that ran a completely different query.

If you are not using EntLib, check if your own code might contain the same bug. Look for the [ThreadStatic] attribute - these are NOT handled by IIS when it decides to migrate logic to a different thread!

Googling "IIS thread agility" ought to turn up more info.

A few links that may be of use:

http://piers7.blogspot.no/2005/11/threadstatic-callcontext-and_02.html and http://blog.idm.fr/2010/03/aspnet-thread-agility-or-why-threadstatic-should-not-be-used.html

answered May 5, 2014 at 14:05

The Dag

1,84116 silver badges23 bronze badges

1 Comment

The Dag Over a year ago

BTW, I forgot to mention that this has been fixed in EntLib 5, so if you are using EntLib 4 upgrading to 5 is likely the easiest way to get rid of the problem.

Collectives™ on Stack Overflow

Getting nonsensical results from SQL query

Updates:

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Updates:

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related