In what situations would you get different users seeing different rows in a table on SQL Server?

Question

SQL Server Version 2000.

We've a bunch of desktops talking to MSSQL Server. When looking for a specific record, some desktops return the correct data, but some do not.

The SQL Command is "SELECT * FROM PODORDH WHERE ([NO]=6141)" On one or two desktops, this returns a record. On the server and on all other desktops, no record is returned.

What areas do I need to look at? What would cause this to happen?

Answer 1

Check which database and server you are connecting to on each machine - the query is simple enough that you must get the same answer everywhere UNLESS you are connecting to different databases or servers.

Answer 2

Double check your connections

Answer 3

This error probably comes from an user who deleted/inserted that record within a transaction but did not yet commit said transaction.

Answer 4

If it is just ONE workstation returning the row then it sounds like that workstation has an open transaction that has not been committed.

Otherwise is it possible that the isolation levels are different for different workstations, ie. some will see uncommitted data and other wont?

Answer 5

You may want to look at the permissions for the table you are selecting from, if you are connecting to the server as a different user from each machine.

If some users but not others have access to read that table, you may get the result you describe.

Answer 6

Perhaps one or two users who find records are using a different schema-name and thus a different tables. IE most users are using dbo.PODORDH, but one or two users are using otheruser.PODORDH.

Answer 7

After you exhaust all the options mentioned above, I would look into row and table locks. If this is the case it should return an error saying it encountered a lock. Are you running an application that could be swallowing errors?