Friday, March 30, 2012

OPENQUERY Informix Dirty Read

Has anyone know of a way to ensure that query on a remote database (Informix
in this case) using OPENQUERY [via a linked server] does not start a
transaction. If the query in is written directly on Informix, you would
issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
However in OPENQUERY you cannot issue:
SELECT *
FROM OPENQUERY(linkinfx,
' SET ISOLATION TO DIRTY READ
SELECT field1
FROM table1
'
I was wondering if an ODBC escape sequence might work:
SELECT *
FROM OPENQUERY(linkinfx,
' {SET ISOLATION TO DIRTY READ}
SELECT field1
FROM table1
'
All though it does not error, I am not sure that it does not start a
transaction. Unfortuanaly, I don't have a local Informix system to test
this against.
The Informix Linked server is set up via ODBC. Preferrable I would like to
set a session level setting on the Linked Server to set the transaction
isolation level to be a read uncommitted value.
Any suggestions would be appreciated.
MikeMichael
Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
"Michael McCallum" <mmccallum@.honovi.com> wrote in message
news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Has anyone know of a way to ensure that query on a remote database
> (Informix in this case) using OPENQUERY [via a linked server] does not
> start a transaction. If the query in is written directly on Informix, you
> would issue 'SET ISOLATION TO DIRTY READ' prior to the Select statement.
> However in OPENQUERY you cannot issue:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' SET ISOLATION TO DIRTY READ
> SELECT field1
> FROM table1
> '
> I was wondering if an ODBC escape sequence might work:
> SELECT *
> FROM OPENQUERY(linkinfx,
> ' {SET ISOLATION TO DIRTY READ}
> SELECT field1
> FROM table1
> '
> All though it does not error, I am not sure that it does not start a
> transaction. Unfortuanaly, I don't have a local Informix system to test
> this against.
> The Informix Linked server is set up via ODBC. Preferrable I would like
> to set a session level setting on the Linked Server to set the transaction
> isolation level to be a read uncommitted value.
> Any suggestions would be appreciated.
> Mike
>|||In SQL Server ti would be Read Uncommitted, in Informix I believe that it is
Dirty Read.
In either case, I am trying to prevent the Informix system (and SQL Server)
from starting a transaction.
Thanks, Mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekROn4lpFHA.3004@.TK2MSFTNGP15.phx.gbl...
> Michael
> Did you mean SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ?
>
> "Michael McCallum" <mmccallum@.honovi.com> wrote in message
> news:Oz%23lwwOpFHA.3960@.TK2MSFTNGP12.phx.gbl...
>

No comments:

Post a Comment