Re: ODBC/SQL*NET on Windows 95

The problem is probably in the MS Jet engine. The inner join syntax is not Oracle syntax, but I believe MS Jet syntax.I suspect that the problem is that the client brings back the results to the client and does the work there. Try using a pass thru query.
Does the pass thru query operate correctly? Jim

<mgilbert271_at_my-deja.com> wrote in message news:94l2lb$oic$1_at_nnrp1.deja.com...
> Can anyone please shine a light on this error please.
>
> Oracle 8.0.5 database with a mixture of Windows 95 and Windows 98
> clients. They are using an Oracle ODBC driver version 8.0.6.1.0
> connecting to a local Access database. Both clients are using SQL*NET
> over TCP. The problem is also replicated on an Oracle 8.1.6.1.0
> database also with Windows 95 clients.
>
> When a "complicated" update statement is issued from the Access
> database we get two results. The update statement follows the lines of
> UPDATE tablename1 INNER JOIN tablename2 ON (tablename1.fieldname1 =
> tablename2.fieldname2) AND (tablename1.fieldname2 =
> tablename2.fieldname2) SET tablename1.fieldname4 =
> tablename2.fieldname4;
>
> On a Windows '98 machine this statement will work fine under any
> circumstance.
>
> On a Windows '95 machine up to the update statement updating about 2800
> rows (16 columns) it's fine. However, anymore than that it returns an
> ODBC Call Error - 3146. Using a SQL*NET trace this has been identified
> as an ORA-00960 error (Ambigious Column Name). Doing an ODBC trace
> identifies where the problem is.
>
> The ODBC trace shows the last command being sent as a SELECT statement
> (with presumambly the UPDATE statement still to come). It is correctly
> selecting the fields, and puts the correct tablenames, but it puts and
> ORDER BY statement. The fields in the SELECT statement are those that
> are in the JOIN. Therefore, when it works, using my previous example,
> the SELECT statement would read:
> SELECT prod.tablename1.fieldname1, prod.tablename2.fieldname2
> FROM prod.tablename1
> ORDER BY prod.tablename1.fieldname1, prod.tablename2.fieldname2
>
> When it doesn't work the ORDER BY statement reads:
> ORDER BY fieldname1, fieldname2
>
> This is producing the ambigious column name error.
>
> I have tried increasing the SDU by fourfold with no success, and using
> the Microsoft for Oracle driver - which I couldn't actually get to
> connect to the database.
>
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Jan 23 2001 - 20:00:47 CST