Posted by DA Morgan on 06/25/05 02:14

Erland Sommarskog wrote:
> Chris (Chris.Tollisen@moorestephens.com) writes:
>
>>In SQL Server 2000 I have set up an Oracle linked server. When I run
>>the following query it runs fine:-
>>
>>SELECT
>> *
>>FROM
>> OPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ORACLETABLENAME')
>>
>>However the following query does not work:-
>>
>>SELECT
>> *
>>FROM
>> OPENQUERY(LINKEDSERVERNAME,'SELECT FIELDNAME FROM ORACLETABLENAME')
>>
>>This error is returned:-
>>
>>Server: Msg 7321, Level 16, State 2, Line 1
>>An error occurred while preparing a query for execution against OLE DB
>>provider 'MSDAORA'.
>>[OLE/DB provider returned message: ORA-00904: "FIELDNAME": invalid
>>identifier
>>]
>>OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
>>returned 0x80040e14].
>>
>>Basically select * works ok, but if I specify the field(s) I need then
>>it errors. I have tried entering the field names in upper and lower
>>case but it makes no difference.
>
>
> Which version of Oracle are you using? MSDAORA does not support Oracle10
> very well. Maybe you should try Oracle's own OLE DB Provider.
>
> Then again, it's very difficult to interpret that message as anything
> else that there is no column FIELDNAME in table ORACLETABLENAME. Yet
> then again, I don't know Oracle.
>
>
>>My real problem is that some dates in the Oracle database are pre 1753
>>which SQL server does not recognise so I need to convert (decode) them.
>
>
> "CAST(datecol AS varchar)" or whichever syntax that is good on
> Oracle for converting date values to strings.

Post at c.d.o.server and include Oracle version, hardware and operating
system, necessary DDL and DML so people will understand the environment.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Back to original message]