Help with DTS for datatransfer from Oracle to SQL server

BeijingPinkGirl · #1 (**permalink**) 10-12-05, 13:46

Greetings,
I have been trying this for a few days still could not make it work. I am new here and please help! Any suggestions/comments are highly appreciated!

I created a simple DTS package in Enterprise Mgr to select a data set from Oracle server and transfer them to SQL 2000 server. Here is the select query for data transfer.

Code:

select * from ord_fx where trade_date='7-Oct-2005'

It works fine and transfer the correct data set.

Now I want to have updated data transfered on daily basis and revise the query:

Code:

declare CurrDate date :=round(sysdate);
select * from ord_FX
where status_Flag is null
and to_date(Entry_date, 'DD-MM-YYYY'>='CurrDate';

However, I got the following error message when I click on Preview... button.

Pat Phelan · #2 (**permalink**) 10-12-05, 14:04

This gets a bit funky to explain, but I'll give it a good shot...

When you compose a query using a linked server within Microsoft SQL, the query itself is interpreted according to the Transact-SQL syntax rules (it is actually done at the OLE-DB level, but the rules are almost exactly the same). The results of that interpretation form the query plan, and an equivalent query is actually passed to the provider for the linked server.

In order to be able to use the Oracle specific syntax like TO_DATE, you'll need to use Microsoft SQL's OPENQUERY function.

-PatP

BeijingPinkGirl · #3 (**permalink**) 10-12-05, 14:32

Pat,
Thank you for your reply. I am not sure if I fully understand.
Just want to be clear attached please see the screenshots I am using to create DTS package by using query. You mean I can use MS SQL language instead of Oracle sytex to transfer data set?

Or I have to create separate SP and call sp from DTS query window?
Many thanks

BeijingPinkGirl · #4 (**permalink**) 10-12-05, 14:33

Here is the screenshots.

Pat Phelan · #5 (**permalink**) 10-13-05, 14:18

Let's continue this in your thread in the Microsoft SQL Forum.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech