Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    16

    Question Unanswered: Help with DTS for datatransfer from Oracle to SQL server

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Aug 2005
    Posts
    16
    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

  4. #4
    Join Date
    Aug 2005
    Posts
    16
    Here is the screenshots.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let's continue this in your thread in the Microsoft SQL Forum.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •