August 11, 2020 at 7:01 pm
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.
August 11, 2020 at 8:34 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 11, 2020 at 9:53 pm
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.
August 12, 2020 at 1:48 am
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
August 12, 2020 at 4:43 pm
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.
August 12, 2020 at 5:01 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply