Linked MS SQL table with large number of rows, query runs too long

[Jeremy Morton]

I am a newbie with using H2.

I have a linked MS SQL 2012 database table, it has about 2 million rows in it.

The "owner" wants us to query using with(nolock), because it is a very active database.  With(nolock) does not seem to be part of H2's grammar.  Is there a way to implement similar results (e.g. the query does not attempt to get a lock on the linked database table dataset?) 

Right now, when I attempt to run something like:

CREATE TABLE CONFIG_LOCAL AS

SELECT *

FROM CONFIG_LINKED

it runs for about an hour at which point I just kill it.  

I was playing around with some of the SET <param> commands I found in this group as well as other places, and was able to get this error message after about 35 min:

Timeout trying to lock table ; SQL statement:

CREATE TABLE CONFIG_LOCAL AS 

SELECT * 

FROM CONFIG_LINKED [50200-193] HYT00/50200

The error with code 50200 is thrown when another connection locked an object longer than the lock timeout set for this connection, or when a deadlock occurred

Any help would be appreciated.

Thank you.

Regards,

Jeremy

[Noel Grandin]

you can use any MS-SQL query string you want with CREATE LINKED TABLE, including NOLOCK

http://h2database.com/html/grammar.html#create_linked_table