openquery worked in SQL 2005 but not in 2008

Question

Post reply

openquery worked in SQL 2005 but not in 2008

Denise McMillan

SSCertifiable

Points: 6216
More actions
August 4, 2011 at 8:51 am

#245588

I have a querythat works in SQL Server 2005, but has an error using it in SQL Server 2008.
select * from openquery (LAWSONPROD,
'SELECT
COMPANY,
JOB_CODE,
DESCRIPTION,
JOB_CLASS,
ACTIVE_FLAG,
CASE WHEN EFFECT_DATE < TO_DATE(''01-JAN-00'') THEN NULL ELSE TO_CHAR(EFFECT_DATE) END,
TO_CHAR(CURRENT_DATE,''DD-MON-YYYY HH:MI'')
FROM PROD.JOBCODE
ORDER BY JOBCODE.JOB_CODE' )
Any ideas why? Thanks very much.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

I have answer to your question. You get the error because something is wrong!

Don't you mind to tell us the error you getting?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Denise McMillan (8/4/2011)
I have a querythat works in SQL Server 2005, but has an error using it in SQL Server 2008.
SELECT *
FROM OPENQUERY(LAWSONPROD, 'SELECT
COMPANY,
JOB_CODE,
DESCRIPTION,
JOB_CLASS,
ACTIVE_FLAG,
CASE
WHEN EFFECT_DATE < TO_DATE(''01-JAN-00'') THEN NULL
ELSE TO_CHAR(EFFECT_DATE)
END,
TO_CHAR(CURRENT_DATE,''DD-MON-YYYY HH:MI'')
FROM PROD.JOBCODE
ORDER BY JOBCODE.JOB_CODE')
Any ideas why? Thanks very much.

I gather that LAWSONPROD links to an Oracle database given the use of TO_CHAR(). Assuming it's a new server hosting SQL 2008 did you install the Oracle drivers and configure TNSNAMES.ORA?

PS I agree with Eugene. When asking for help to resolve an error, it's common courtesy to post the actual error message 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 3

I'm sorry, I forgot to put that in! Error is:

OLE DB provider "OraOLEDB.Oracle" for linked server "LAWSONPROD" returned message "ORA-01858: a non-numeric character was found where a numeric was expected".

Thanks again.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Denise McMillan (8/4/2011)
I'm sorry, I forgot to put that in! Error is:
OLE DB provider "OraOLEDB.Oracle" for linked server "LAWSONPROD" returned message "ORA-01858: a non-numeric character was found where a numeric was expected".
Thanks again.

It happens 🙂 Thanks for posting back.

I guess this could be an OLEDB problem, however this seems to come straight from the Oracle engine. <throwing_mud>Have you run this query directly on the Oracle database? Just to rule out the query itself and isolate that it is in fact a SQL Server problem?</throwing_mud>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

You get an Oracle error, it is nothing to do with SQL Server.

Can you execute this query successfully in Oracle itself?

Do you have up-to-date OracleOledb driver?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Try expliciting the date format in TO_DATE / TO_CHAR.

Maybe you installed the client with different locale settings and the dates don't get parsed/converted correctly.

-- Gianluca Sartori

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 7

Yes, it is linked server to an Oracle database. And also yes, it's a new server with Oracle client and tnsnames in place. Thanks so much.

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 8

Here's what worked:

select * from openquery (LAWSONPROD,

'SELECT

COMPANY,

JOB_CODE,

DESCRIPTION,

JOB_CLASS,

ACTIVE_FLAG,

CASE WHEN EFFECT_DATE < TO_DATE(''01-JAN-00'', ''DD-MON-YY'') THEN NULL

ELSE TO_CHAR(EFFECT_DATE) END as effectdate,

TO_CHAR(CURRENT_DATE,''DD-MON-YYYY HH:MI AM'') as currdate

FROM PROD.JOBCODE

ORDER BY JOBCODE.JOB_CODE' )

Thanks very much for all ideas.

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

spaghettidba

SSC Guru

Points: 105732

August 5, 2011 at 9:31 am

#1364445

Glad you sorted it out.

-- Gianluca Sartori

shrirohit Grasshopper Points: 22 More actions · Answer 10

Hi All,

After spending hours I found it has to do with how "date" datatypes are handled in Oracle and MS SQL. Putting this function TO_DATE ('DD-MON-YYYY', 'DD-MON-RRRR') arpund all date datatypes in Oracle views have solved the problem. Replace DD-MON-RRRR with required date.

Hope this will help.

shrirohit Grasshopper Points: 22 More actions · Answer 11

Hi All,

After spending hours I found it has to do with how "date" datatypes are handled in Oracle and MS SQL. Putting this function TO_DATE ('DD-MON-YYYY', 'DD-MON-RRRR') arpund all date datatypes in Oracle views have solved the problem. Replace DD-MON-RRRR with required date.

Hope this will help.