We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Linked server with ODBC only returns the top row for a select statement

Medium Priority
1,743 Views
Last Modified: 2013-12-18
Hi all,

I have setup a linked server using ODBC to connect to an Oracle database, I can execute inserts, updates and deletes without problem but when I query the database it only gives me the first row of the table, eg:

SET ROWCOUNT 0
SELECT * FROM OPENQUERY(PerformOEE_OLink,'SELECT * FROM ud_downtime ORDER BY tester')

This returns one row.

I have setup the linked server previously using OLE DB for Oracle and everything worked fine, but this may not be available on another server which I need to configure.

Any help is appreciated.

Thanks.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
the table does have more than 1 record, and despite the ROWCOUNT 0 , you get only 1 record ?!
without any errors?

I can only presume you point the linked server to the wrong database? ...

Author

Commented:
The linked server is pointing at the right database.

If I use a where clause in my select statement I can select all of the different records and return them individually.

The only problem I'm having is listing more than one record from a select statement. Everything else if fine.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Well, I am kind of relieved about rowcount...  Are there any binary type columns ?

What do you get if you do :

EXEC sp_tables_ex MyLinkedSvrName

hopefully you see catalogue,schema, tablename for the table you want to run the query against. in which case, can you please try that four part identifier :

select count(*) from linkedsvr.catalogue.schema.tablename

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Sounds very much like a server setting, or the odbc setup to restrict the result set being returned... What versions are you using ?

There is a connection setting "RST" and needs to be set to "T" meaning RST = Result sets T = that result sets are to be enabled.  

Author

Commented:
If I use the following SELECT statement I get an error:

SELECT * FROM PerformOEE_OLink..SYSMAN.UD_DOWNTIME

Msg 7318, Level 16, State 1, Line 3
The OLE DB provider "MSDASQL" for linked server "PerformOEE_OLink" returned an invalid column definition for table ""SYSMAN"."UD_DOWNTIME"".

Not sure what version of ODBC it is, but there is no RST setting. There is an Enable Result Sets option but it is already ticked, and I have tried unticking and ticking it.

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Well, your are still not using it properly. Need all 4 parts. Still missing catalogue / database name...

So, what version of Oracle ? all we know is 10.x from the tags - maybe 10.2 ?
How did you install the odbc driver - is it one from Oracle - did you install the client tools ?

At this point I don't think I can help, so might need to leave it for other experts...

Commented:
Are you sure you are using the correct user? In Oracle, SYSMAN is a db admin account used by the Enterprise Manager

Author

Commented:
I added tables to the database using the SYSMAN account, so the account is right. I am using oracle 10.2 and installed the aministrator version of the Oracle client.

Everything works if I uise the OLE DB for Oracle connection but not the ODBC connection which suggests it's an ODBC issue. I am using Windows XP Pro, could there be an issue with MDAC, If I get the latest version, will this help?
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Agree with the prognosis - I would be looking at the ODBC drivers / setup...

MDAC - It could help - which version are you currently using ? Also the ODBC driver should be Oracles version for 10.2, install with the client tools. Might want to check for patches - recall that post in the other thread ? Think there was some mention of a patch ;)

Windows XP Pro should not be an issue - though Vista and 64-bit probably would add to the possible causes especially 64bit SQL on 64bit OpSys (does not have any compatable odbc drivers - according to microsoft two weeks ago helping someone here in EE).



Author

Commented:
I have updated the oracle driver. This did not solve the problem. I'm now at a complete loss! I dont have access to download the patch from oracle, unless anyone knows of another location to get it from?
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
No, just what was on that thread - doubt it would exist anywhere else.

There are third party ODBC drivers for Oracle - apparantly better than anything from MS or Oracle - but they cost money... datadirect, easysoft, openlink, do a quick google on : oracle 10.2 ODBC drivers

For example : http://www.datadirect.com/products/odbc/odbc32/index.ssp  can download a trial and see if it works for you. If not, then there is probably something else going on...

Might also be worth having a look at some of the data pump type tools and see if they have problems returning rowsets - might help isolate the problem - more so than provide a solution as to how to update...

http://www.microsoft.com/sqlserver/2005/en/us/migration-oracle.aspx

or

http://www.swissql.com/products/oracle-to-sqlserver/oracle-to-sql-server.html

Again they will not help day to day, but just to help narrow down the problem...

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
I downloaded a trial driver from DataDirect and it works!

Thanks for your assistance mark_wills, I would give you a million points if I could.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
good job, mark!
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Thanks guys, glad that it worked out for you...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.