October 15, 2013 at 1:17 am
I have a 64 bit SQL Server 2005 Standurd edition installed on my server, I am trying to query Oracle 11g database
I have a link server configured say LINK1 using 'OLEDB Provider for Oracle' Provider.
The connection is working fine.
I get the results for the below query
select * from openquery(LINK1, select * from all_objects)
BUT, when I try querying views on the Oracle 11g database using the opensource query, I get blank rows... I do not get any error... just blank rows.
The same query returns records when executed directly at Oracle end.
Can any one give me sloution to this so that I can see the view data while using open query
much appreciated...
October 15, 2013 at 5:49 am
I would start by checking the Oracle permissions of the user being used to connect to Oracle from SQL. You're using all_objects, which is a view of all objects to which the user has access. If the user you're connecting with doesn't have permission to any database objects on the Oracle side, then no rows will be returned.
October 15, 2013 at 6:58 am
Thank you for your reply... but this doesnot seem to be a permission/access issue... as I get the view structure.. but not the data.. it says (0 rows found).
I am using Oracle provider for OLEAD as I cannot found Microsoft OLEDB provider for Oracle in 64bit standurd edition
October 15, 2013 at 7:03 am
umesh.war.rao (10/15/2013)
Thank you for your reply... but this doesnot seem to be a permission/access issue... as I get the view structure.. but not the data.. it says (0 rows found).I am using Oracle provider for OLEAD as I cannot found Microsoft OLEDB provider for Oracle in 64bit standurd edition
I didn't mean permission to all_objects - all users can query user_objects and all_objects. I meant the other database objects the user has permissions to. Whatever objects the user owns will show up in user_objects and whatever objects the user has access to will show up in all_objects. So, if you grant select on a table to the Oracle user in Oracle, you should be able to see that object in all_objects.
October 15, 2013 at 7:30 am
I have another instance that is of 32 bit Standurd edition and on that I have a linked server configured that uses the same user credentials to connect to the Oracle database.
On this instance, when I run the same query I get the all the records (1000 rows).
But on the new 64 bit standurd edition when I run the same query it gives me the table structure but not the data..
In the 32 bit std edition I use the 'Microsoft OLEDB provider for Oracle' and as I cannot find this provider in 64 bit std edition I use the 'Oracle provider for OLEDB' for the Linked server configuration.
Is there some issue with the provider that I am using..
October 15, 2013 at 9:21 am
'Microsoft OLEDB provider for Oracle' is not supported on 64-bit platforms. The Oracle driver should work fine though. The fact that you are getting "0 rows found" suggests your Oracle server is processing the query. I'd suggest checking permissions as well, and double-check your query to make sure there isn't a typo or an extra WHERE clause or something that is filtering your results.
October 15, 2013 at 9:27 am
Do you have a string literal being implicitly converted to a date?
Something like WHERE TABLE.Start_Date = '2013-10-15'?
If so, you might want to try changing it to WHERE TABLE.Start_Date = TO_DATE('06/30/2012','MM/DD/YYYY') instead. It looks like some people have had problems with implicit date conversions when passed through SQL Server links.
October 15, 2013 at 11:10 am
sestell1 (10/15/2013)
Do you have a string literal being implicitly converted to a date?Something like WHERE TABLE.Start_Date = '2013-10-15'?
If so, you might want to try changing it to WHERE TABLE.Start_Date = TO_DATE('06/30/2012','MM/DD/YYYY') instead. It looks like some people have had problems with implicit date conversions when passed through SQL Server links.
That's a good point. Not all data types are compatible between SQL Server and Oracle. Here's Oracle's data type map: http://docs.oracle.com/html/B10544_01/apa.htm.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply