Uncommited Reads

Peter Czurak

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

I have created a stored procedure which retrieves data from a linked server.
The linked server is an Informix database - I want to set the query so it
reads an uncommitted records. I have set SQL7.0 to read uncommitted record
which seems to be working, but how can I set Informix database to read
uncommitted records? The informix systax to read uncommitted records is 'Set
Isolation dirty read' I belive (I am not sure if that's the right syntax,
but I am more concerned with where do I put this code not the syntax)

Here is my stored procedure:

CREATE PROCEDURE MyProc AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT Test.*
FROM OPENQUERY(MyLinkedServer, "SELECT Table1.* From Table1) As Test

Thanks

Peter
pcz...@bigfoot.com

BPMargolin

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

Peter,

The following is only a guess, since I have to admit that I have not faced a
situation similar to yours ...

Check out the section "OPENQUERY (T-SQL)" in the SQL Server 7.0 Books Online ...
the OpenQuery syntax looks like it might be able to handle "SET ISOLATION DIRTY
READ SELECT ..."

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.

Peter Czurak <pcz...@bigfoot.com> wrote in message
news:snm9p0...@corp.supernews.com...

Peter Czurak

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

Thanks for your help. I am new to this so I am not sure what you mean. I
have looked at the Books Online and this is what I found:
Syntax
OPENQUERY(linked_server, 'query')

Arguments
linked_server
Is an identifier representing the name of the linked server.
'query'
Is the query string executed in the linked server.
I don't see a place where I can specify the type of read. As the case in
SQL7.0 you can not set the read type in a SELECT statement (I think)

Thanks

Peter
pcz...@bigfoot.com

"BPMargolin" <bpm...@attglobal.net> wrote in message
news:e5P6YJN9$GA.296@cppssbbsa04...

Umachandar Jayachandran

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

You can send any statement to the linked server. The only restriction is
that OPENQUERY should be able to obtain the meta-data of the resultset by
using the OLE-DB / ODBC functions.
So you can do:

SELECT Test.*
FROM OPENQUERY( MyLinkedServer,
'SET ISOLATION LEVEL DIRTY READ
SELECT Table1.* From Table1'
) As Test

If you do TRANSACTION ISOLATION LEVEL in SQL Server, I believe it
affects only the local tables.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
(Please reply only to the newsgroup)

Peter Czurak

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

Thanks for your help!

When I use this code

SELECT Test.*
FROM OPENQUERY( MyLinkedServer,
'SET ISOLATION LEVEL DIRTY READ
SELECT Table1.* From Table1')
As Test

I get the follwing error:
-------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 7321: An error occurred while preparing a query for execution against
OLE DB provider 'MSDASQL'.
A syntax error has occurred.]
---------------------------
OK
---------------------------
.

SET ISOLATION LEVEL DIRTY READ is the correct syntax on Informix database

Thanks

Peter
pcz...@bigfoot.com

"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:O9CTmiN9$GA...@cppssbbsa02.microsoft.com...

Kalen Delaney

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

The SQL Server Books Online will tell you that to put a connection into
'dirty read' mode, you use the following:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
www.TheHungerSite.com

Peter Czurak <pcz...@bigfoot.com> wrote in message

news:snmmqke...@corp.supernews.com...

Umachandar Jayachandran

unread,

Jul 23, 2000, 3:00:00 AM7/23/00

to

I gather that MyLinkedServer is an Informix database. If so, please
ensure that you typed the two statements correctly. It looks like you have a
syntax error. Maybe you need a batch terminator or something for the SET to
take effect. I do not know.
Try the statements directly against the Informix database first & then
use it in the OPENQUERY function.

D. Patrick Hoerter

unread,

Jul 24, 2000, 3:00:00 AM7/24/00

to

Kalen,

His linked server is an Informix db.

D. Patrick Hoerter

Kalen Delaney wrote in message ...