Re: Dirty Read
From: "Peter Czurak" <pczurak@bigfoot.com>
>
>I have created a stored procedure in SQL7.0 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/locked 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 syntax to read uncommitted records is
>'set
>isolation to dirty read' . Does anyone know where I put the 'set isolation
>to dirty read' (what is the syntax) or how can I read the locked records
>from Informix through SQL7.0. I have the 'SET TRANSACTION ISOLATION LEVEL
>READ UNCOMMITTED' in the stored procedure but that is setting the SQL7.0
>not
>the Informix
>
>I can read the same records through Visual Basic program and ADO.
>
>Here is my stored procedure in SQL7.0:
>
>CREATE PROCEDURE MyProc AS>
>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>
>SELECT Test.*
>FROM OPENQUERY(MyLinkedServer, "SELECT Table1.* From Table1") As Test
>
>
>--------------------------------------------------
>
>I have tried the following:
>
>CREATE PROCEDURE MyProc AS>
>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>
>SELECT Test.*
>FROM OPENQUERY(MyLinkedServer, "set isolation to dirty read
>SELECT Table1.* From Table1") As Test
>
>---------------------------------------------
>
>I have tried single and double quotes, I have tried quotes around the 'set
>isolation to dirty read' statement but still getting an error message. I
>have tried 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED' inside the
>OPENQUERY with same results. I get the following error message.
>
>
>
>[Microsoft][ODBC SQL Server Driver][SQL Server]An error occurred while
>preparing a query for execution against OLE DB provider 'MSDASQL'.
>[OLE/DB provider returned message: [Informix][Informix ODBC
>Driver][Informix]A syntax error has occurred.]
I have no idea if this will work, but have you tried:
SELECT Test.*
FROM OPENQUERY(MyLinkedServer, "set isolation to dirty read ;
SELECT Table1.* From Table1") As Test
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com