Dirty Read

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.] Thanks Peter pczurak@bigfoot.com

In article <snokj2l5jjt177@corp.supernews.com>, "Peter Czurak" <pczurak@bigfoot.com> wrote: > 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 don't know how OPENQUERY works in SQLServer, but I would think this is close. Try "set isolation to dirty read; select Table1.* from Table1". Note the semicolon (;) after dirty read. The set isolation is one statement that works for the whole transaction, so it needs to be separated from the select statement. > > --------------------------------------------- > > 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.] > > Thanks > > Peter > pczurak@bigfoot.com > > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.