<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Informix Linked Server problems
12 posts, Page 1 of 2
1
2
»»
Informix Linked Server problems
Rate Topic
Display Mode
Topic Options
Author
Message
jameswhitby
jameswhitby
Posted Wednesday, April 21, 2010 4:13 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, July 17, 2012 8:57 AM
Points: 9,
Visits: 81
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
Post #907592
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Wednesday, April 21, 2010 8:30 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 3,163,
Visits: 4,342
This error is usually encountered when the linked server user does not have select access to the catalogue tables (irrespective of whether it has been granted access to the required tables / views).
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #907766
Lowell
Lowell
Posted Wednesday, April 21, 2010 8:36 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 11,790,
Visits: 28,067
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
--
There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #907773
jameswhitby
jameswhitby
Posted Thursday, April 22, 2010 2:26 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, July 17, 2012 8:57 AM
Points: 9,
Visits: 81
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.
Post #908391
Lowell
Lowell
Posted Thursday, April 22, 2010 4:11 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 11,790,
Visits: 28,067
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
--
There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #908440
jameswhitby
jameswhitby
Posted Thursday, April 22, 2010 4:20 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, July 17, 2012 8:57 AM
Points: 9,
Visits: 81
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".
Post #908442
Lowell
Lowell
Posted Thursday, April 22, 2010 4:25 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 11,790,
Visits: 28,067
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
--
There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #908446
jameswhitby
jameswhitby
Posted Thursday, April 22, 2010 4:29 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, July 17, 2012 8:57 AM
Points: 9,
Visits: 81
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
Post #908448
nagainbox
nagainbox
Posted Wednesday, September 08, 2010 4:02 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 15, 2012 3:34 AM
Points: 2,
Visits: 131
Hi
I am also facing the same issue, if you have found the solution , could you please share it here?
Thank you
Regards
Naga
Post #982157
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Wednesday, September 08, 2010 4:40 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 3,163,
Visits: 4,342
Request the Informix dba to check what level of access the linked server user has to the catalogue views / tables
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #982177
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
