Inconsistent Results with SQL Server

Question

I'm having some trouble with a SQL Server 2008 R2 (Express) installation. It seems like there are two tables operating in "parallel".

If I run the following query:

USE [database];
SELECT * FROM [dbo].[events] WHERE 1=1;

I get the results I expect. However, if I run this query, I get a dataset that is completely wrong:

SELECT * FROM [database].[dbo].[events] WHERE 1=1;

Data is being sent into the database by a variety of programs -- so obviously one (or more) of these apps is doing something wrong. The apps connect to database via a C# LINQ --> SQL DBML, the DBML (which I cannot modify) uses events as the table name (ideally it would use dbo.events, but I can't modify/recreate it.)

Has anyone seen this before? Is there some way to make the two queries equivalent? I can recreate the database if necessary - the data in there is not critical.

If I load up the database in SQL Management Studio and right click to select "Select top [1000] rows" -- it executes the second query (the one that gives me the wrong results...).

Edit:

There is only 1 instance in play (as far as I know anyway -- don't have access to the server right now, will check tomorrow)
The two tables are totally different. Query 1 results in 2,000 or so items. Query 2 results in 20,000 or so items. All items are unique.

In what sense "completely wrong"? I'd start off by checking the rowcounts as they should be identical. If they are identical yhen I'd stick and ORDER BY on the selects. Although the probability is that SQL Server is querying the data in exactly the same way, it's a horrendously quick sanity check. Once that's checked out then it's worth looking at other possibilities (e.g., you aren't running multiple instances of SQL on one server and connecting to different ones). Basically what you're seeing just sounds wrong from a fairly fundamental level, so it's worth checking all the basics first. — Callie J
– Callie J, Commented Jan 12, 2012 at 1:01
Well, your select top1000 in sql studio cannot return 2000 results... Do you not limit your result set somewhere else also? What hapens if you execute select count(*) both ways? — Pleun
– Pleun, Commented Jan 12, 2012 at 9:50
The numbers I provided are from a count(*) query. There's nothing else limiting the table... — debracey
– debracey, Commented Jan 12, 2012 at 14:13

debracey · Accepted Answer · 2012-01-12 14:20:19Z

1

It looks like Martin is correct. Someone messed up this particular server and created tables with the exact same names in a different database, then set some incorrect settings to make the bad database the default.

Deleting the bogus database fixed the problem.

answered Jan 12, 2012 at 14:20

debracey

6,6372 gold badges32 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

Inconsistent Results with SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related