| Author |
Topic |
|
anaji
Starting Member
15 Posts |
 Posted - 2012-01-12 : 05:06:50
|
| Gurus,i have created an openquery between oracle and sql,when i execute the following codeselect * from openquery (dbname,'select*from tablename)it only returns the first record, im facing this issue with some tables and some work just fine and retrieve all the records,any idea |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-12 : 06:38:58
|
| do you've access to oracle db server? if yes, try directly connecting to from TOAD or any client tool and running the query to see whether you're getting the same result.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
anaji
Starting Member
15 Posts |
Posted - 2012-01-15 : 00:41:54
|
| there is no issue when running from TOAD , it gives back all records, the issue i face is only when connecting from SQL, i've checked ODBC connection that i made to see if there is any option i'm missing but no luck |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-15 : 02:09:29
|
| is there some setting you've done in linked server settings to give only first result?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
anaji
Starting Member
15 Posts |
Posted - 2012-01-16 : 00:42:34
|
| that's the thing i didn't do anything, and other tables are going just fine, same settings same everthing when i query it it gives me all the rows but with certain tables it's only getting the first one, also if i query with a where condition it will also show me 1 record only diffrent than the first record it shows when i query with * with no condition, which means it's looking through the table , |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-16 : 01:04:29
|
| one more question. are you sure that query is hitting table itself and not a view? my doubt is you're having view on top of table with some additional logic and your query is using it instead of actual table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-16 : 01:14:22
|
| Another guess is same table existing on different schemas and your default schema is not the one having the data you're expecting. so when you say just tablename its getting data from your default schema whereas what you really want is data from table with same name but in different schema------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
anaji
Starting Member
15 Posts |
Posted - 2012-01-16 : 01:36:53
|
| well regarding the views thing yes it was pointing to a view that i created from TOAD to SQL, when i faced this issue i have created a table not a view but also same issue, showing 1 record only, the thing is in TOAD when i query the same exact query it gives me all rows i need and i'm sure i'm using the same in both TOAD and SQL, i'm really out of ideas here, i was thinking maybe there is an OS issue causing the ODBC driver to go crazy on me or something, i appreciate your help here visakh.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-16 : 02:03:07
|
| oh...TOAD also you're getting all rows. Then I definitely feel it has something to do with ODBC setting------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
openquery returning only 1 record