multiplied columns from Oracle linked server

  • cphite

    SSCarpal Tunnel

    Points: 4124

     

    Wasn't sure exactly where to put this one...

    We populate an Oracle data warehouse each night, and we connect to it via Linked Server connection.  It works perfectly well for the most part.

    One weird thing though...  when I want to look at the structure of a table on the Oracle side, I go to the table via the Management Studio -- so basically Linked Servers -->  <Server Name> --> Catalogs --> Default --> Tables --> then right-click on the table name and choose "Script table as SELECT FROM..."  it returns the table column, but it repeats them.  The primary key column is repeated twice, and the other columns are repeated six times each.

    The query it creates is "right" insofar as it includes all of the columns and in the right order - it's just duplicating them.

    I am not sure if this is a SSMS issue, or an Oracle issue.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23165

    My expectation - this is a non-issue.  Scripting tables across a linked server that changes database technologies is likely to produce weird results.  BUT if you want to blame someone for it - I think it would be the driver you are using to make the linked server more than SSMS or Oracle.  The driver is what is used to push and pull the data across, so it sounds like a bug in your version of the driver.

  • cphite

    SSCarpal Tunnel

    Points: 4124

    I was thinking along the same lines...  and agree, it's not a serious issue - more of an annoyance.  Was just curious to see if anyone had seen it before and had a workaround.

    We're using the OraOLEDB.Oracle driver.

    Interestingly enough, I've found some patterns - integer columns only repeat twice, and numeric columns don't repeat at all.  Varchar columns repeat either four or six times - but I haven't found any specific reason between it being four or six.

    Fortunately, it doesn't seem to affect normal queries - I can still use SELECT * FROM for example, and it doesn't duplicate anything.  It's only when looking for the schema information.

  • sterling3721

    SSC-Addicted

    Points: 469

    I observed the same when using Oracle ODAC to set up linked server.  Plus, there was a memory leak when using the Oracle 12.2 driver,  I deinstalled and installed Oracle 12.1 and ISServerExec.exe didnt go above 1GB and job ran successfully.

    My personal experience and from https://social.msdn.microsoft.com/Forums/en-US/5fa0d6a5-08d8-428d-b9b3-2417e47ae006/isserverexecexe-consuming-all-memory-and-ssis-job-failing-quickly?forum=sqlintegrationservices

  • cphite

    SSCarpal Tunnel

    Points: 4124

    We are using Oracle 12.2

    However, I am not using SSIS for anything related to this Oracle connection, specifically because I couldn't get SSIS to connect.  Not sure if that is related to the issue you experienced or something else.  I could connect via command line, and via SQL linked Server, but anything I tried to do with SSIS was timing out.

     

     

  • sterling3721

    SSC-Addicted

    Points: 469

    not sure. On my dev box with 32GB memory, with 12.2 driver, SSIS took >30GB; with 12.1 driver, it's less than 1GB. I didn't go back to ODAC 12.2 since the memory issue was resolved and everything worked nicely.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply