1

We're trying to use READPAST in a SQL select statement to extract data from a SQL Server 2008 database using QlikView, which is set up to use OLEDB connection to the database.

The reason for this being that we want to avoid being locked by other processes but also don't want to read any uncommitted data - otherwise we'd be using NOLOCK.

We tested the approach in SSMS initially - starting a transaction, adding a row, then separately querying the table with READPAST. This didn't return the uncommitted row as we'd want

We then added this to our OLEDB SQL query (same query, same database) in QlikView and ran the code. This time it waited for the transaction to be closed (committed or rolled back) before it finished the query.

We also tried with ODBC and SQL Native Client that are both supported by QlikView but got the same results.

We also tried with NOLOCK as the hint instead and this performs as expected - it returns the uncommitted row in both SSMS and QlikView.

Any idea why this would work in SSMS and not via OLEDB/ODBC/SQLNC? Is there a configuration option on the database or the connection that needs changing?

x3ja
  • 928
  • 5
  • 19
  • I am not familiar with QlikView, but you may wish to check the transaction isolation level for the OLEDB connection, or specify it explicitly set it, `READPAST` will only work with `READ COMMITTED` or `REPEATABLE READ` isolation levels. – GarethD Oct 21 '15 at 15:57
  • Thanks - we'd tried this too in fact. Puzzled as to why SSMS doesn't need this for READPAST to work if something else might though - does it have a different default transaction isolation level? According to https://msdn.microsoft.com/en-us/library/ms131673.aspx the default isolation level for SQLNC OLEDB is `READ COMMITTED` anyway (at least in SQL Server 2016, I can't find info for 2008)... but didn't work for us. – x3ja Oct 22 '15 at 10:56

0 Answers0