Re: Oracle ODBC and SQL Server 7

Sorry - yes I mean the Oracle OLE DB provider although I see the same results as the Microsoft OLE DB provider for Oracle.

The stored procedure runs fine in Oracle so I presume there must be a problem with the OLE DB translation?

Unfortunately the communication must be from SQL Server to Oracle so Im trying to find a work around.

The only idea Ive come up with is have SQL Server insert or update a table containing very few rows (so that the speeds are fast enough to be workable) and have a local trigger that does the operation

In article <8ucjeb$dfa$1_at_nnrp1.deja.com>,   jocave_at_my-deja.com wrote:
> In article <8ubqok$lvj$1_at_nnrp1.deja.com>,
> roffster_at_my-deja.com wrote:
> > I am trying to set up a system whereby I keep 2 tables (one under
 SQL
> > Server 7 and the other in Oracle 8.0.6) in synch, so that
> > inserts,updates and deletes in the SQL Server tables are
 communicated
> > to the Oracle tables.
> >
> > I have set up a linked server in SQL Server using the Oracle OLE
 ODBC
> > driver and I can insert,update and delete through this linked server
 to
> > the Oracle tables...however the Oracle tables are holding upwards of
> > 500K rows. I discovered that when the Oracle tables are empty the
> > inserts etc take very little time to complete however when the
 Oracle
> > tables are full of the appropriate data an update could take upwards
 of
> > 3 minutes, making the whole system unworkable!
> >
> > Ive subsequently discovered that the calls via the ODBC link first
 do
 a
> > SELECT * FROM ... before inserting,updating or deleting, which
 explains
> > why the whole process took so long to complete.
> >
> > Does anyone have any suggestions (or encountered this before)?
>
> Can you clarify what exactly you mean by "Oracle OLE ODBC driver"? If
> you're connecting via OLE DB, you'll be using one of
> - Oracle OLE DB Provider
> - Microsoft OLE DB Provider for Oracle
> - Microsoft OLE DB Provider for ODBC
>
> If you're using the third option, which ODBC driver are you using
> (Oracle ODBC driver, Microsoft ODBC driver for Oracle, or a third-
 party
> ODBC driver for Oracle).
>
> I'm rather clueless about how one links to an Oracle table from
> SQL*Server, so I'll assume that you use some sort of adaptor with
> limited config options. If this isn't the case, let me know. I'll
> also assume that it's this adaptor which is generating the
> obnoxious "select *" statement.
>
> You may also want to try going the opposite way (linking the
 SQL*Server
> table from Oracle). In my (admittedly limited) experience with the
> Oracle Transparent Gateways, I haven't seen them generate this sort of
> really obnoxious SQL.
>
> >
> > To get over this Ive decided to write an Oracle procedure that does
 the
> > updates etc and only call this procedure via the ODBC link.
> >
> > However when I try and call the procedure from SQL Server (by
 EXECUTE
> > <linked server name>..<user>.<procedure name>) I get the following
> > error:-
> >
> > [OLE/DB provider returned message: [Oracle][ODBC][Ora]ORA-06550:
 line
> > 1, column 30:
> > PLS-00103: Encountered the symbol "1" when expecting one of the
> > following:
> >
> > begin declare end exception exit for goto if loop mod null
> > pragma raise return select update while <an identifier>
> > <a double-quoted delimited-identifier> <a bind variable> <<
> > close current delete fetch lock insert open rollback
> > savepoint set sql commit <a single-quoted SQL string>
> > The symbol "return" was substituted for "1" to continue.
> > ]
>
> I assume you're able to call this procedure correctly from within
> Oracle. Generally, ORA-06550 errors indicate that the procedure
 failed
> to compile correctly.
>
> --
> Justin Cave - Oracle ODBC Development
>
> Opinions expressed herein are my own and may not reflect those of
> Oracle Corporation.
>
> --
> Justin Cave - Oracle ODBC Development
>
> Opinions expressed herein are my own and may not reflect those of
> Oracle Corporation.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 09 2000 - 05:02:45 CST