Microsoft SQL Server forum - SQL Openquery & Oracle

Author

SQL Openquery & Oracle

Chris

2005-06-24, 7:23 am

In SQL Server 2000 I have set up an Oracle linked server. When I run
the following query it runs fine:-

SELECT
*
FROM
OPENQUERY(LINKEDSERV
ERNAME,'SELECT * FROM ORACLETABLENAME')

However the following query does not work:-

SELECT
*
FROM
OPENQUERY(LINKEDSERV
ERNAME,'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::Pre
pare
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.

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.

Any help would be appreciated.

Thanks
Chris

Erland Sommarskog

2005-06-24, 8:23 pm

Chris (Chris. Tollisen@moorestephe
ns.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(LINKEDSERV
ERNAME,'SELECT * FROM ORACLETABLENAME')
>
> However the following query does not work:-
>
> SELECT
> *
> FROM
> OPENQUERY(LINKEDSERV
ERNAME,'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::Pre
pare
> 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/techin.../2000/books.asp

DA Morgan

2005-06-24, 8:23 pm

Erland Sommarskog wrote:
> Chris (Chris. Tollisen@moorestephe
ns.com) writes:
>
>
>
> 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.
>
>
>
>
> "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)

Sponsored Links