• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1599
  • Last Modified:

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

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.
0
resourcesys
Asked:
resourcesys
  • 6
  • 5
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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? ...
0
 
resourcesysAuthor 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.
0
 
Mark WillsTopic AdvisorCommented:
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

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Mark WillsTopic AdvisorCommented:
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.  
0
 
resourcesysAuthor 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.

0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
gatorvipCommented:
Are you sure you are using the correct user? In Oracle, SYSMAN is a db admin account used by the Enterprise Manager
0
 
resourcesysAuthor 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?
0
 
Mark WillsTopic AdvisorCommented:
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).



0
 
resourcesysAuthor 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?
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
resourcesysAuthor 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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
good job, mark!
0
 
Mark WillsTopic AdvisorCommented:
Thanks guys, glad that it worked out for you...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now