June 28, 2010 at 2:16 am
I have a client with our application installed on SQL Server 2005 (64 bit) Enterprise Edition (SP3).
We have been asked to write an SSRS report which displays data from Oracle (32-bit, may be version 10g?) and SQL Server. My plan was to use OPENROWSET and pass the connection details to the report such that we don't have to hard-code the connection details into the report.
I had the client install the 64-bit Oracle OLEDB drivers on the server and tried an OPENROWSET query. It was a simple SELECT query which should have returned only 32 records, however the query just hung (I killed it after about 4 minutes). After this I ended up with SQL Server using > 20GB of RAM on the server, and it stopped accepting all new connections to the database. I ended up stopping and starting the SQL Server service which fixed the problem.
Has anyone successfully connected SQL Server 2005 64-bit to Oracle?
June 30, 2010 at 9:00 am
I had the client install the 64-bit Oracle OLEDB drivers on the server and tried an OPENROWSET query. It was a simple SELECT query which should have returned only 32 records, however the query just hung (I killed it after about 4 minutes). After this I ended up with SQL Server using > 20GB of RAM on the server, and it stopped accepting all new connections to the database. I ended up stopping and starting the SQL Server service which fixed the problem.
Has anyone successfully connected SQL Server 2005 64-bit to Oracle?
I don't understand what you mean by Oracle OLE DB driver because all development connections to Oracle requires the full Oracle client install. When the Oracle client and TNSNAMES.ORA file are both in the server running SQL Server it is just a simple connection. So install both x64 and x86 Oracle client and put the TNSNAMES.ORA file in the SSRS development folder, I am not sure it is related to resources.
Kind regards,
Gift Peddie
June 30, 2010 at 5:51 pm
We had the drivers installed from here: http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html
Once this was done, I was able to access a new linked server provider in SQL Server, namely OraOLEDB.Oracle, which I was using in an OPENROWSET query.
However once I ran a simple query to test this, the SQL Server started using about 21GB of memory and would not accept any new connections. I had to stop and start the service, after which the memory usage returned to about 1.5GB and we could make new connections again.
June 30, 2010 at 7:02 pm
Jenny Richardson (6/30/2010)
We had the drivers installed from here: http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.htmlOnce this was done, I was able to access a new linked server provider in SQL Server, namely OraOLEDB.Oracle, which I was using in an OPENROWSET query.
However once I ran a simple query to test this, the SQL Server started using about 21GB of memory and would not accept any new connections. I had to stop and start the service, after which the memory usage returned to about 1.5GB and we could make new connections again.
You need to install the full Oracle client in both x64 and x86 because you can use x86 Oracle and BIDS is x86 so you need to install both. You also need to add the TNSNAMES.ORA file in the folder you used to install the full Oracle client. If you are in Windows 2003 you also need to run IIS6 in x86 mode, in Windows 2008 you need to run the application pool connecting to Oracle in x86 mode. In Windows 2008 run the SSRS with specific account in IIS 7
Kind regards,
Gift Peddie
August 8, 2010 at 10:31 pm
Hi,
Sorry it's taken me a while to get back to this. We had a work-around using a 32-bit server but the client is wanting to use 64-bit for all servers going forward so it looks like this may not be possible.
Re: the 32-bit client - I'm not sure why we would need this. We are not using BIDS on the reports server as we have to develop locally, then deploy the RDL to a remote server on another network.
Also - I was testing my OPENROWSET query by running it in SSMS, and this is where I was experiencing the memory problems.
Thanks
Jenny
August 15, 2010 at 4:21 pm
We have been asked to write an SSRS report which displays data from Oracle (32-bit, may be version 10g?) and SQL Server. My plan was to use OPENROWSET and pass the connection details to the report such that we don't have to hard-code the connection details into the report.
I cannot understand why you think you should use Oracle x64 client when you are connecting to x86 Oracle 10g. That said I know current oracle client require x86 client installed for most Microsoft development. And x86 build but that is not relevant to you because you are just deploying code.
Kind regards,
Gift Peddie
August 15, 2010 at 6:30 pm
I am using a 64-bit SQL Server and the 32-bit driver wasn't showing up as a linked server provider. When I installed the 64-bit driver then it appeared in the list.
August 16, 2010 at 7:17 am
Not sure about the x86 vs. x64 driver issue, I'll do some testing today and see if I can come up with anything.
Regarding the query that was used against the Oracle datasource, was there a where clause involved, or is the table in Oracle only 32 rows?
August 16, 2010 at 7:38 am
That was the entire table - it's a small reference table I was using to test the connectivity.
Thanks for any testing you are able to do - I have limited (i.e. NONE) Oracle resources in-house, and so to test anything I need to keep asking our client to do various things. Add to that the server is "semi-production" so it's not a great idea if I keep having to restart it...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply