Thanks for responding Richard, I was in a hurry so as well as posting to this mailing list I kept on looking for a solution. A person called David Browne finally gave me the explanation of my problem. It is because of a difference between Oracle and Sql Server regarding transactions. For those who are interested here is what David told me:
" ... This is because of Oracle's multi-version concurrency. Oracle will satisfy your query from the old data retrieved from the undo logs. Uncommited changes are always silently ignored in Oracle. In SQLServer you must choose between seeing uncommited changes and waiting for the changes to be commited or rolled back. In other words Oracle gives you a snapshot of the data as of the last point in time when it was consistent, and SQLServer gives you a shapshot of the data as of the next point in time when it was consistent. The kicker is that the next point in time may be in the future, and you will just have to wait. > - Am I using the isolation level in a bad way? Yes. It's the not the isolation level of the data-changing connection that matters, rather it's the isolation level of the data-reading transaction. The data-reader can choose to ignore the data-changer's locks and read the uncommitted data, or can wait until the locks are released. To read through the locks, set the transaction isolation level of the reader or use the (nolock) query hint. > - Is it a limitation of SQL Server? Yes. If you want to see a snapshot from before your transaction, you must read the data before your transaction. You may be able to simply fill a DataTable with a shapshot of the data before you start changing data. ... " It's bad news for me (that would be too long to explain why), but I can at least take appropriate action based on that limitation of Sql Server. Jerome
