March 13, 2009 at 3:20 pm
Hi all,
I 've 2 questions regarding a query in linked server .
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 without the date selection parameter but, when I use the 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
it throws an error Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '01'.
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 using the same linked server ?
Thank you for the help ….
March 17, 2009 at 12:35 pm
Try creating reports in SSRS using 4Part names (ServerName.DatabaseName.SchemaName.TableorViewName)
March 26, 2009 at 9:02 am
What version of Oracle are you connecting to?
And...any reason why you are not using the Oracle Provider for Oracle instead of the Microsoft provider? It's a night and day difference when it comes to speed. I'd be willing to bet that it's 10 times faster. It may not show up as an option if you install the default oracle client. You may need to bring up the Oracle client installer again and do a custom install. This will allow you to add the OLE objects and components and MS transaction server component. When you are done adding the components, you must reboot the server.
March 30, 2009 at 8:10 am
...to_date('31-JAN-2009','ddMONYYYY' ' )... syntax is not correct.
It should be like to_date('31-JAN-2009','dd-mon-yyyy')
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply