unordained
2011-02-10 18:08:02 UTC
I've got another team trying to access my firebird database from sqlserver, using
'openquery' (they're pulling from themselves, me, and another access database, and that
seemed like the cleanest solution at the time.)
Their queries are purely read-only queries.
At the moment, my database is mostly locked up because of a migration script running;
almost all records are being touched by a very long-running process.
It seems in the ODBC configuration GUI that their only options result in either:
a) something like read-committed + no-rec-version + nowait, which blows up with
deadlock errors (because it's told not to wait on the new record versions, which won't
be available possibly for days)
b) something like read-committed + no-rec-version + wait, which just hangs forever
(because my code's not going to commit for days.)
Neither of those is good.
I'm not sure what transaction settings they're using in sqlserver, and I haven't found
any documentation about how sqlserver configures openquery (based on the sqlserver
transaction settings?), and how that passes through to the ODBC layer -- but is there
any way I can get them to use the transaction mode I want, which is just a plain read-
committed, rec-version (wait won't matter), or what I always use, snapshot isolation? I
don't see anywhere to configure exactly that in the ODBC GUI, and what we change there
*seems* to have an effect, so regardless of sqlserver/openquery, what we configure
matters -- but I can't configure what I want. Is there a registry setting, a text file,
some other place you can configure stuff like this?
I can't tell if they can do anything like SET TRANSACTION inside an OpenQuery call --
my impression was that it had to be a single, valid statement (a select/insert/etc.)
and that other command types weren't allowed to be mixed in. And that each one is
independent, so they can't use one OpenQuery for SET TRANSACTION, and another for
SELECT. But I'm not sqlserver guru.
Thanks,
-Philip
'openquery' (they're pulling from themselves, me, and another access database, and that
seemed like the cleanest solution at the time.)
Their queries are purely read-only queries.
At the moment, my database is mostly locked up because of a migration script running;
almost all records are being touched by a very long-running process.
It seems in the ODBC configuration GUI that their only options result in either:
a) something like read-committed + no-rec-version + nowait, which blows up with
deadlock errors (because it's told not to wait on the new record versions, which won't
be available possibly for days)
b) something like read-committed + no-rec-version + wait, which just hangs forever
(because my code's not going to commit for days.)
Neither of those is good.
I'm not sure what transaction settings they're using in sqlserver, and I haven't found
any documentation about how sqlserver configures openquery (based on the sqlserver
transaction settings?), and how that passes through to the ODBC layer -- but is there
any way I can get them to use the transaction mode I want, which is just a plain read-
committed, rec-version (wait won't matter), or what I always use, snapshot isolation? I
don't see anywhere to configure exactly that in the ODBC GUI, and what we change there
*seems* to have an effect, so regardless of sqlserver/openquery, what we configure
matters -- but I can't configure what I want. Is there a registry setting, a text file,
some other place you can configure stuff like this?
I can't tell if they can do anything like SET TRANSACTION inside an OpenQuery call --
my impression was that it had to be a single, valid statement (a select/insert/etc.)
and that other command types weren't allowed to be mixed in. And that each one is
independent, so they can't use one OpenQuery for SET TRANSACTION, and another for
SELECT. But I'm not sqlserver guru.
Thanks,
-Philip