Informix Linked Server problems

Question

Post reply

Informix Linked Server problems

jameswhitby

SSC-Addicted

Points: 465
More actions
April 21, 2010 at 4:13 am

#219499

I have been asked to set up a few procs that will take data from our core data source in Informix and compare the values with our data warehouse which is SQL Server 2008.
The Linked Server works fine and connects no problem, but whenever I try to run a simple select I get a few errors that are driving me nuts.
The Simple select I'm trying to test first is:
select * from LIVE.live_db.informix.thistable
This is correct (I've messed about with it a bit hence the stupid naming) but when I run it I get the following message:
OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message "EIX000: (-111) ISAM error: no record found.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "Ifxoledbc" for linked server "LIVE". The provider supports the interface, but returns a failure code when it is used.
Has anyone had any experience with these kind of errors before as it's driving me nuts.
Cheers
Jim
April 21, 2010 at 8:30 am

This was removed by the editor as SPAM
September 8, 2010 at 4:40 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

try this command first:

EXEC sp_tables_ex LinkedServerName

that should give you a list of the tables the login you are using can view/has access to;

if the linked server has access to more than one database, the other databases will not appear, only his 'default' database;

that might help you diagnose, as identified above, wheter the login you are using has access to the tables in question.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jameswhitby SSC-Addicted Points: 465 More actions · Answer 2

I ran that proc and it gave me a list of all the tables including the one I was testing the link against.

So from what I can see, the linked server connects perfectly well, can see all the tables that I'd expect, I can even generate a query to a new window via the Object Explorer Details (hence it can see the columns on the Informix database). But the second I try and run it, it falls over when trying to return the data.

Lowell SSC Guru Points: 323504 More actions · Answer 3

james it's probably just the dbname.schemaname then;

you said you were running this commnad:

select * from LIVE.live_db.informix.thistable

try

select * from LIVE...thistable instead; that lets the linked server infer the dbname and schemaname, and just use the linkedserver and table; i'd bet that is your issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jameswhitby SSC-Addicted Points: 465 More actions · Answer 4

Lowell,

By doing that I get the following error message:

Msg 7313, Level 16, State 1, Line 1

An invalid schema or catalog was specified for the provider "Ifxoledbc" for linked server "LIVE".

Lowell SSC Guru Points: 323504 More actions · Answer 5

jameswhitby (4/22/2010)
Lowell,
By doing that I get the following error message:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "Ifxoledbc" for linked server "LIVE".

i'm learning a bit myself here....i did not know informaix had multiple schemas; i've only done linked servers to oracle adn the usual access/excel/textfolder so far;

how about letting it infer just the db name?

select * from LIVE..informix.thistable

select * from LIVE.live_db..thistable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jameswhitby SSC-Addicted Points: 465 More actions · Answer 6

Lowell,

Same error as before.

It appears that it has to be told exactly where to go, then when it goes there, that is when the failure occurs.

I think it's all down to the security side of Informix, which is all down to our dba.

In fact, I'll have another word with him as this is making me understand where there problem lies a lot better

Cheer

Jim

nagainbox SSC Enthusiast Points: 114 More actions · Answer 7

Hi

I am also facing the same issue, if you have found the solution , could you please share it here?

Thank you

Regards

Naga

ourss Grasshopper Points: 11 More actions · Answer 8

This error could occur if the coledbp.sql script has been run against the sysmaster database. See the link below for more information.

How to set up and test a simple OLE DB Linked Server in Microsoft® SQL Server to allow retrieval of data in an IBM Informix database server

madion SSC Enthusiast Points: 153 More actions · Answer 9

I know this is an old thread, but I was running into the same problem and managed to find a solution by adding to Lowell's post:

Lowell (4/22/2010)

jameswhitby (4/22/2010)
Lowell,
how about letting it infer just the db name?
select * from LIVE..informix.thistable
select * from LIVE.live_db..thistable

What I did is:

select * from [linked_server].[catalog].[schema].[table_name].

I assume the author as moved on to something else but it might be worth it for future reference...