Problem with sql query using linked server

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 2005 Forums

Analysis Server and Reporting Services (2005)

Problem with sql query using linked server

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2009-03-16 : 11:17:47

I created a linked server using Microsoft OLE DB Provider for Oracle. I created an inner join query in Sql Server Management Studio.

The inner join query works without the date selection parameter but, when I use the date selection parameter
it throws an error Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '01'.

AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN to_date('01-JAN-2009', 'dd-MON-YYYY') AND to_date('31-JAN-2009','dd-MON-YYYY' ' ) AS a

I’ve tried every possible way to solve it by changing the date format but, nothing seems to work.

SELECT a.* FROM OPENQUERY(TESTORALINK,'SELECT MASTER50_ENT.ORDERS.ORDER_ID, _

MASTER50_ENT.ACCOUNT.EXTERNAL_ACCOUNT_NUMBER, _

MASTER50_ENT.CONTACT.COMPANY, _

MASTER50_ENT.ACCOUNT.COUNTRY_CD

FROM MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT

WHERE MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID

AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID

AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN to_date('01-JAN-2009', 'dd-MON-YYYY') AND to_date('31-JAN-2009','ddMONYYYY' ' ) AS a

Also, another question is how create a report in SSRS 2005 using the same linked server ?

Thank you for the help ….

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 11:38:26

Try this,


SELECT * FROM OPENQUERY(TESTORALINK,	
'SELECT 
	MASTER50_ENT.ORDERS.ORDER_ID,
	_MASTER50_ENT.ACCOUNT.EXTERNAL_ACCOUNT_NUMBER, _MASTER50_ENT.CONTACT.COMPANY, _MASTER50_ENT.ACCOUNT.COUNTRY_CD 
FROM 
	MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT 
WHERE 
	MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID 
	AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID 
	AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN to_date(''01-JAN-2009'', ''dd-MON-YYYY'') 
	AND to_date(''31-JAN-2009'',''dd-MON-YYYY'')
')

swenri
Yak Posting Veteran

72 Posts

Posted - 2009-03-16 : 12:04:01

No, it doesn't work. It keeps throwing the same error. Any clues ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:06:21

is this sql server? i dont think to_date is a sql server function.

swenri
Yak Posting Veteran

72 Posts

Posted - 2009-03-16 : 13:25:45

Yes, I'm running a Select query in Sql Server 2005, where the tables are Oracle 10g linked tables. I removed the to_date function , I still keep getting the error as below. Can you please throw some light on this ???

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '01'.

SELECT * FROM OPENQUERY(TESTORALINK,'SELECT MASTER50_ENT.ORDERS.ORDER_ID,
_MASTER50_ENT_ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,
_MASTER50_ENT.CONTACT.COMPANY,
_MASTER50_ENT.ACCOUNT.COUNTRY_CD
FROM
MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT
WHERE
MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID
AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID
AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN ('01-JAN-2009', 'dd-MON-YYYY')
AND ('31-JAN-2009','dd-MON-YYYY')
')

Thanks a lot !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:39:06

try like this:-


SELECT * FROM OPENQUERY(TESTORALINK,'SELECT MASTER50_ENT.ORDERS.ORDER_ID,
_MASTER50_ENT_ACCOUNT.EXTERNAL_ACCOUNT_NUMBER, 
_MASTER50_ENT.CONTACT.COMPANY,
_MASTER50_ENT.ACCOUNT.COUNTRY_CD 
FROM 
MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT 
WHERE 
MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID 
AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID 
AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN ''20090101''
AND ''20090131''
')

swenri
Yak Posting Veteran

72 Posts

Posted - 2009-03-16 : 13:55:13

I tried it, I get this error now..

OLE DB provider "MSDAORA" for linked server "TESTORALINK" returned message "ORA-01861: literal does not match format string
".

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT MASTER50_ENT.ORDERS.ORDER_ID,MASTER50_ENT.ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,MASTER50_ENT.CONTACT.COMPANY,MASTER50_ENT.ACCOUNT.COUNTRY_CD
FROM MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT
WHERE MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID
AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID
AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN '20090101'
AND '20090131'
" against OLE DB provider "MSDAORA" for linked server "TESTORALINK".

Please help ....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 14:00:40

quote:
Originally posted by swenri

I tried it, I get this error now..

OLE DB provider "MSDAORA" for linked server "TESTORALINK" returned message "ORA-01861: literal does not match format string
".

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT MASTER50_ENT.ORDERS.ORDER_ID,MASTER50_ENT.ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,MASTER50_ENT.CONTACT.COMPANY,MASTER50_ENT.ACCOUNT.COUNTRY_CD
FROM MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT
WHERE MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID
AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID
AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN ''20090101''
AND ''20090131''
" against OLE DB provider "MSDAORA" for linked server "TESTORALINK".

Please help ....

this is not what i posted. see the two ' around date values

swenri
Yak Posting Veteran

72 Posts

Posted - 2009-03-16 : 14:54:41

I tried the exact same as you had posted. Yet again I get the same error.... Appreciate all your help.

OLE DB provider "MSDAORA" for linked server "TESTORALINK" returned message "ORA-01861: literal does not match format string
".
Msg 7320, Level 16, State 2, Line 1

SELECT * FROM OPENQUERY(TESTORALINK,'SELECT MASTER50_ENT.ORDERS.ORDER_ID,MASTER50_ENT.ACCOUNT.EXTERNAL_ACCOUNT_NUMBER,MASTER50_ENT.CONTACT.COMPANY,MASTER50_ENT.ACCOUNT.COUNTRY_CD
FROM
MASTER50_ENT.ACCOUNT,MASTER50_ENT.ORDERS,MASTER50_ENT.CONTACT
WHERE
MASTER50_ENT.ORDERS.ACCOUNT_ID = MASTER50_ENT.ACCOUNT.ACCOUNT_ID
AND MASTER50_ENT.ACCOUNT.CONTACT_ID = MASTER50_ENT.CONTACT.CONTACT_ID
AND (MASTER50_ENT.ORDERS.ORDER_DTM) BETWEEN ''20090101''
AND ''20090131''

')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources