Openquery:Oracle to SQL Error.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Openquery:Oracle to SQL Error.

Author

Topic

kowani1
Starting Member

42 Posts

Posted - 2007-11-01 : 17:25:44

I am trying to run an open query from SQL 2000 to an Oracle 9i Database and am getting the following error:
ORA-00904: "DD-MON-YYYY HH24:MI:SS": invalid identifier

The openquery is:

SELECT *
FROM OPENQUERY(INFOLAB, 'SELECT JOB_ID,
TO_CHAR(DATE_SAMPLED,"DD-MON-YYYY HH24:MI:SS")AS JOBS_Date_Sampled
FROM OTML.JOBS')

Please help!!

Jungle DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-01 : 17:34:13

Does your query (without openquery) work if you run it directly on Oracle?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kowani1
Starting Member

42 Posts

Posted - 2007-11-01 : 18:42:15

Running the same query in Oracle (SQLPLUS) does run successfully.
i.e.
SQL> select to_char(DATE_SAMPLED, 'DD-MON-YYYY HH24:MI:SS') as JOBS_date_Sampled
from jobs where job_id = 'SH02309';

JOBS_DATE_SAMPLED
--------------------
17-MAR-2006 00:00:00

Just not sure why it's not running from SQL via the openquery.

Jungle DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-01 : 18:53:04

Try a different driver.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources