Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005

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

    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.
    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:
    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.

    Note: I have moved this post from SQL site to this SQL Server site which is more appropriate. Thank you for your help in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'm picking up the discussion from your thread in the SQL Forum.

    If you use the OPENQUERY function, the query you enter as a string argument will be passed verbatim to the target server for execution. In that case, your Oracle specific TO_DATE() syntax will be executed in the Oracle environment and should produce the results that you expect.

    If you do not use OPENQUERY, and compose the query using the linked server in a four-part-name, then that query will be parsed and an execution plan will be forumlated by OLE-DB (think of it as SQL Server for most purposes, although it is a great deal more complex if you want to get into the details). The query plan will be built locally (on the machine running SQL Server), and some variant of your query will be passed to the remote machine for execution. This implies that SQL Server needs to be able to parse your query when you do it this way. It also implies that you'll get a query plan based on a "bigger picture" than what you'd get via OPENQUERY, which is usually (but not always) a better plan.


Posting Permissions

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