SQL Openquery & Oracle

[Chris]

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.

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]

Chris (Ch************@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, es****@sommarskog.se

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

[DA Morgan]

Erland Sommarskog wrote:

Chris (Ch************@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
da******@x.washington.edu
(replace x with u to respond)