Posted by Erland Sommarskog on 06/25/05 01:25

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.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]