locked
Linked to Oracle Server. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I am working on a project that requires me to link SQL Server 2008 R2 to Oracle 11g. I have installed the Oracle client on the SQL Server and made several test connections. I am able to list data from Oracle in the SQL Server Manger. I want to make sure I impact the Oracle server the least I can. I don't want my connection to prevent backups or the shutting down of Oracle. I would like Oracle to act as if I am just another users. With that in mind is it better for me to use OpenQuery or execute functions in oracle that return a table to SQL Server, or should I just create a script in SQL Server and execute it against the Oracle database using OLEDB. What will be the least intrusive to the Oracle systems so their DBA doesn't complain that my connection is preventing backups or prevents oracle from being shut down. Thanks.
    Wednesday, February 12, 2014 4:13 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    How about SSIS package or Import export wizard is your DML big or small generally for Big DML i would recommend SSIS or imp/exp wizard.Youcan use  OLEDB provider to connect to Oracle database or when you create connection.

    Regarding load it depends on task you do .

    Coming to your linked server with oen query there are some limitation

    http://technet.microsoft.com/en-us/library/ms188427.aspx

    If DML is small ,few rows ,I would like you to access oracle with Linked server and using fully qualified name .If your query is light load will be light and yes oracle will treat it as connection

    >> so their DBA doesn't complain that my connection is preventing backups or prevents oracle from being shut down. Thanks.

    That would be foolish of him to say unless you run some bad query.But backup being disturbed by query is not possible in Oracle.They have cold backup and hot if cold is there DB instance is shutdown so anyhow you wont be able to connect

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Marked as answer by tracycai Tuesday, February 18, 2014 8:10 AM
    • Edited by Shanky_621MVP Tuesday, February 18, 2014 8:49 AM
    Wednesday, February 12, 2014 4:39 PM