December 5, 2011 at 2:39 pm
I am in the process of migrating SQL Server 2005/Visual Studio 2005 running on a 32-bit server to SQL Server 2008R2/Visual Studio 2008R2 running on a 64-bit server. Both the 32-bit and 64-bit versions of the Oracle 11.2.0 client are installed on the 64-bit environment. A Linked Server is set up using the OraOLEDB.Oracle provider and the connection test succeeds. The “Allow inprocess” option is checked on the provider.
I have about 9 SSIS packages that access an Oracle 10 database via this Linked Server and the OPENQUERY command eg [ SELECT * FROM OPENQUERY(‘LinkedServer’, ‘SELECT * FROM OracleView’) ]. All of the packages work fine in the 32-bit environment using this same logic. About half of the packages seemed to convert fine and they are running on the 64-bit machine correctly.
I have two questions/problems that I hope to get some insight on:
1) All of the SSIS packages on the 64-bit server appear to be actually executing the OPENQUERY command when I try to edit the OLE DB Source object of the Data Flow in BIDS. The longest of these queries runs for over 2 hours on the 32-bit machine; the shortest is only a few seconds. So, when I got to the longer running queries, it takes forever for the edit window to appear for the Source. As far as I know, none of this happened in the 32-bit environment. If I have to wait for the query to execute (as long as 2 hours), my development time skyrockets. Did I miss something in the setup of the Linked Server or other item?
2) The packages that have the longer query runtimes are not pulling any rows from Oracle. I have been battling with one for several days. This query normally runs for about 5 minutes pulling about 90 fields and about 25000 rows. I ran some tests by executing the OPENQUERY command in SSMS and the results are the same-no rows.
I did some internet research and found a few references to row limits, but the suggestions did not fix my problem. I ran more tests using the TOP qualifier, but the results are inconsistent. I can get 1 row to result, but if I go to TOP 5, no rows result. I can change back to 1 row, and no rows result.
I also found some references to a data type issue (NUMERIC as I recall). So, I copied the Oracle view to remove all number fields and to pull only about 15 fields. I used that modified view in SSMS, but again no rows came over.
The odd thing here is that the queries that DO work also have numeric fields, but do not seem to produce the problems described in the articles I found. So, I am not sure if this data type issue is even part of my problem.
This is very confusing as to why some of the queries are working and not others using the same commands.
Can someone offer some pointers as to why this is happening?
Thanks.
===================
I believe I stumbled on the solution to both of my issues. Basically, I set up an ODBC connection to the Oracle system. This uses the installed Oracle driver and connects via the TNSNAMES entry. I then set up the Linked Server using the "Microsoft OLE DB Provider for ODBC Drivers" provider. I used Product Name "Oracle" and the Data Source is the name of the ODBC connection.
Now the OPENQUERY in BIDS pulls the expected number of rows. The editor opens quickly for debugging and development.
I have no idea why this is working, but I guess I am happy it is.
March 2, 2012 at 1:56 am
First, eliminate all suspicious things and make smallest possible test example.
E.g. create oracle table with just one varchar2 column and populate it with data (or find one, or select just a varchar2 field from some table).
Then try to access this table ove db link.
Try to play with oracle provider settings (Linked Servers->Providers->OraOLEDB.Oracle->Properties) - e.g. have a checkbox only in "inprocess" parameter.
Play with linked server properties: e.g. "Enable promotion of distributed transactions" set to "false", and "RPC" and "RPC out" set to "true".
Some say you need to restart sql server service for this setting to take effect (not sure, try on test machine not to restart production without real need).
Windows account that is executing sql server service should have full access rights to folder with oracle client (e.g. "C:\Oracle").
Try using oracle-syntax queries like this:
EXECUTE('SELECT * FROM DUAL') AT YOURLINKEDSERVERNAME
In my experience, I achieved fastest possible results with oracle with this. It is fast as you are directly using oracle.
Just one more thing. Type "NUMBER" is not the same as type "NUMBER(32)" or "INT" or "NUMBER(15,2)". So, if you have a type problem, look at this "subtle" differences which may cause you a problem.
Hope this helps,
Vedran
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply