Dear SQL Server community,
I now have figured out what is going on and a solution.
Query Analyser simply does not work with Distributed
Transactions/Linked Servers.
BUT the DTS Query Designer does !!
Try DTS on a package, create an Execute SQL task with a
SQL Server connection and Browse
query to go into DTS Query Designer the same query works.
This means one can do do a join between a SQL Server 2000
database and an ORACLE database. This is a very important
and useful facility.
I stumbled on this being new to DTS and I was quite
disappointed no one in the User community advised this.
- Phil Pinto
>-----Original Message-----
>MT,
>Thanks for the reply but I have already tried your
>suggestion (and checked cases)
>
>eg 1 Adding table owner where TTT was table owner
> Select USERNAME from RTXNL1..TTT.APP_USER
>Server: Msg 7391, Level 16, State 1, Line 1
>The operation could not be performed because the OLE DB
>provider 'MSDAORA' was unable to begin a distributed
>transaction.
>
>eg 2 Adding schema part where SSS is schema
> Select USERNAME from RTXNL1.SSS.TTT.APP_USER
>Server: Msg 7312, Level 16, State 1, Line 1
>Invalid use of schema and/or catalog for OLE DB
>provider 'MSDAORA'. A four-part name was supplied, but
the
>provider does not expose the necessary interfaces to use
a
>catalog and/or schema.
>
>Should this be logged with Microsoft ?
>Does any one know a different ORACLE provider that works ?
>
>The main reason I was trying this out was because I want
to
>seperately use DTS to do a SQL join between an ORACLE
>database and a SQL server 2000 database. Any one out
there
>done this ?
>It should work according to a DTS WROX book I have but
>this is firstly dependant on the ORACLE distributed
>transaction working !
>(I plan a circumvention using one DTS ORACLE enquiry to
>SQL SERVER temporary table and then do a subsequent SQL
>SERVER join but this is not ideal).
>
>- Phil
>
>>-----Original Message-----
>>Have you tried adding the Schema owner/table owner to
the
>>query? (Be sure all cases match. Oracle is case-
>>sensitive)
>>
>>For instance, under Oracle, presume the user under which
>>the table APP_USER was created is PHIL, then the query
>>would be
>>
>>Select USERNAME from RTXNL1..PHIL.APP_USER
>>
>>
>>MT
>>
>>>-----Original Message-----
>>>Example 1
>>>---------
>>>select USERNAME from RTXNL1...APP_USER
>>>-- USERNAME is VARCHAR2(30)
>>>
>>>Example 2
>>>---------
>>>select USER_ID from RTXNL1...APP_USER
>>>-- USER_ID is NUMBER(12)
>>>
>>>Example 3
>>>---------
>>>select XXX from RTXNL1...APP_USER
>>>-- XXX column name does not even exist
>>>
>>>All return error below.
>>>I have also tried other linked ORACLE databases.
>>>
>>>
>>>>-----Original Message-----
>>>>What is your SQL statement? What is the column type?
>>>The
>>>>datatype you are trying to pull may not be SQL Server
>>>>compatible.
>>>>
>>>>
>>>>MT
>>>>
>>>>>-----Original Message-----
>>>>>I have a linked server and see the tables but cannot
>>>>>retrieve data via Query Analyser on SQL Server 2000
>>>>>
>>>>>Server: Msg 7318, Level 16, State 1, Line 1
>>>>>OLE DB provider 'MSDAORA' returned an invalid column
>>>>>definition.
>>>>>
>>>>>Any one else met this problem ?
>>>>>If so, any solution.
>>>>>Thanks in advance.
>>>>>.
>>>>>
>>>>.
>>>>
>>>.
>>>
>>.
>>
>.
>

Report this post to a moderator