Linked Server problems with SQL Server 2008.
A user setting up a SQL Server 2008 linked server to Informix via the Ifxoledbc OLE DB provider could connect, but any query failed with "-111 ISAM error: no record found" and "Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO", or with "-201 syntax error / cannot open the table" when using SSMS-generated four-part-name SELECTs. Suggestions offered: run the coledbp.sql script to install the OLE DB interface procedures (the DBA had already done this), check for fragmented tables lacking rowids, use OPENQUERY instead of four-part naming, verify table owner/permissions, and drop the SQL Server square-bracket delimiters (the poster said removing them made no difference). The thread ends with no resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
Did you install the OLE interface tables? You need to run the SQL script
coledbp.sql to do that. See this link:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Apr 21, 2010 at 6:27 AM, JAMES WHITBY <
jameswhitby@manorhousecottage.com> wrote:
> 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
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd14b185aaf710484bd6099
Yes, these were all done by our Informix DBA on all databases.
Can I also add that I have used the Object Explorer Details tool in SQL Server Management Studio, viewed the listed tables and got it to generate a select script for me to use to be on the safe side. When running this I get the following message which leads me to believe the problem is on the Informix side: OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message "E42000: (-201) A syntax error has occurred.". Msg 7306, Level 16, State 2, Line 1 Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider "Ifxoledbc" for linked server "LIVE". The specified table or view does not exist or contains errors.
This is directed to all posters, not you in particular: PLEASE PLEASE PLEASE!!!!!!!!! When you reply to a post, and especially when you respond to a reply, quote enough of the posting that you are replying to so that we know what you are talking about!!!!!!!!!!!!!!! Most of us do NOT follow these forums on the forum readers but using the email gateway. These postings do not thread will in email readers and even if they did, we delete the forum posts as soon as we read them. I have over 6000 saved emails in my inbox and that's just the things I cannot delete! If I saved every forum and CDI posting I'd never find what I need to service my clients! So, PLEASE PLEASE PLEASE!!!!!!!! I'll assume that you were answering my suggestion that you run the OLE setup script, but that is not clear. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 21, 2010 at 7:55 AM, JAMES WHITBY < jameswhitby@manorhousecottage.com> wrote: > Yes, these were all done by our Informix DBA on all databases. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0b9c52ca7fb0484bf0829
Better?
MESSAGE 2:
Yes, these were all done by our Informix DBA on all databases.
Can I also add that I have used the Object Explorer Details tool in SQL Server
Management Studio, viewed the listed tables and got it to generate a select
script for me to use to be on the safe side.
When running this I get the following message which leads me to believe the
problem is on the Informix side:
OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message "E42000:
(-201) A syntax error has occurred.".
Msg 7306, Level 16, State 2, Line 1
Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
"Ifxoledbc" for linked server "LIVE". The specified table or view does not
exist or contains errors.
REPLY 1:
Did you install the OLE interface tables? You need to run the SQL script
coledbp.sql to do that. See this link:
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
Art
MESSAGE 1:
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
James,
This sounds familar to me. But I was not running SQL 08. We had an issue
with linked server and frag'd tables and rowid's. Are your informix tables
frag'd? If so, you could rebuild the table with rowids.
Also, it appears you are using SQL four part naming to query your tables.
In previous vers of Squeal they recommended openquery statements for
performance reasons.
Good luck and let me know how it turns out.
Thanks
========================
Darren Jacobs
Sr Analyst, Architecture Team
Darren_Jacobs@carmax.com
804.747.0422 x3221
========================
From: "JAMES WHITBY" <jameswhitby@manorhousecottage.com>
To: ids@iiug.org
Date: 04/21/2010 06:28 AM
Subject: Linked Server problems with SQL Server 2008. [19768]
Sent by: ids-bounces@iiug.org
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
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
MUCH!
the ANSI erro 42000 and Informix error -201 indicate a syntax error. Can
you post the query that caused this, maybe we'll see something. I haven't
done any linked server queries with MS SQL Server myself, and don't
generally do OLE, .NET or other PC protocols, but I know it all should
work.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Apr 21, 2010 at 9:32 AM, JAMES WHITBY <
jameswhitby@manorhousecottage.com> wrote:
> Better?
>
> MESSAGE 2:
>
> Yes, these were all done by our Informix DBA on all databases.
>
> Can I also add that I have used the Object Explorer Details tool in SQL
> Server
> Management Studio, viewed the listed tables and got it to generate a select
> script for me to use to be on the safe side.
>
> When running this I get the following message which leads me to believe the
> problem is on the Informix side:
>
> OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message
> "E42000:
> (-201) A syntax error has occurred.".
>
> Msg 7306, Level 16, State 2, Line 1
>
> Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
> "Ifxoledbc" for linked server "LIVE". The specified table or view does not
> exist or contains errors.
>
> REPLY 1:
>
> Did you install the OLE interface tables? You need to run the SQL script
> coledbp.sql to do that. See this link:
>
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
>
> Art
>
> MESSAGE 1:
>
> 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
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd2e2027ad1f20484bfef96
1. On the SQL Server table, try to grant it the appropriate privilege to the
other user that is on the other server, for example, informix.
2. Make sure the owner of the SQL Server table is actually "informix" (or
"dbo"?).
Just my guess.
----- Original Message ----
From: JAMES WHITBY <jameswhitby@manorhousecottage.com>
To: ids@iiug.org
Sent: Wed, April 21, 2010 6:27:52 AM
Subject: Linked Server problems with SQL Server 2008. [19768]
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
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Darren,
We're trying to get the data from the Informix tables to SQL Server, and one
of the main problems that there are no row id's on any of the tables, hence
reporting and maintaining the data is becoming a nightmare.
I'm relatively new to the company and am not entirely sure how the tables were
originally designed but from what I've seen they're non-indexed,
non-relational and pretty appalling.
I'll pass this onto our Informix guys to confirm that the tables are designed
like this then I can stop banging my head against a brick wall.
Cheer
Jim
===================================================================
James,
This sounds familar to me. But I was not running SQL 08. We had an issue
with linked server and frag'd tables and rowid's. Are your informix tables
frag'd? If so, you could rebuild the table with rowids.
Also, it appears you are using SQL four part naming to query your tables.
In previous vers of Squeal they recommended openquery statements for
performance reasons.
Good luck and let me know how it turns out.
Thanks
========================
Darren Jacobs
Sr Analyst, Architecture Team
Darren_Jacobs@carmax.com
804.747.0422 x3221
========================
From: "JAMES WHITBY" <jameswhitby@manorhousecottage.com>
To: ids@iiug.org
Date: 04/21/2010 06:28 AM
Subject: Linked Server problems with SQL Server 2008. [19768]
Sent by: ids-bounces@iiug.org
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
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Art,
The query I'm using was auto generated by SQL Servers Object Explorer Details
tool, it found the tables on informix, created a select statement but when it
ran it threw an error!!!
The query was simply:
SELECT [rsk_code]
,[rsk_descr]
,[rsk_sort_order]
,[equity_deal]
,[gilt_deal]
,[bond_deal]
,[with_charge]
,[wdl_stock_exception]
,[check_cgt]
,[legacy]
,[model]
,[portfoliotypeid]
,[rsk_transferred]
,[rsk_target]
FROM [LIVE].[Live_db].[owner].[risks]
** I changed some names for security reasons.
The baffling thing is that SQL Server can see the tables, create a simple
query to grab the data but when it tries to access the data it falls over.
===============================================================
MUCH!
the ANSI erro 42000 and Informix error -201 indicate a syntax error. Can
you post the query that caused this, maybe we'll see something. I haven't
done any linked server queries with MS SQL Server myself, and don't
generally do OLE, .NET or other PC protocols, but I know it all should
work.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Apr 21, 2010 at 9:32 AM, JAMES WHITBY <
jameswhitby@manorhousecottage.com> wrote:
> Better?
>
> MESSAGE 2:
>
> Yes, these were all done by our Informix DBA on all databases.
>
> Can I also add that I have used the Object Explorer Details tool in SQL
> Server
> Management Studio, viewed the listed tables and got it to generate a select
> script for me to use to be on the safe side.
>
> When running this I get the following message which leads me to believe the
> problem is on the Informix side:
>
> OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message
> "E42000:
> (-201) A syntax error has occurred.".
>
> Msg 7306, Level 16, State 2, Line 1
>
> Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
> "Ifxoledbc" for linked server "LIVE". The specified table or view does not
> exist or contains errors.
>
> REPLY 1:
>
> Did you install the OLE interface tables? You need to run the SQL script
> coledbp.sql to do that. See this link:
>
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
>
> Art
>
> MESSAGE 1:
>
> 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
>
>
Are all of those square brackets really there? If so then maybe that's your
syntax error, because it's certainly not part of standard ANSI SQL which is
what Informix understands! I know that the OLE and .NET providers do some
simple syntax mapping from MS SQL Server paradigms to standard, but I don't
think that removing all those extraneous brackets is among those mappings.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Apr 22, 2010 at 3:46 AM, JAMES WHITBY <
jameswhitby@manorhousecottage.com> wrote:
> Art,
>
> The query I'm using was auto generated by SQL Servers Object Explorer
> Details
> tool, it found the tables on informix, created a select statement but when
> it
> ran it threw an error!!!
>
> The query was simply:
>
> SELECT [rsk_code]
>
> ,[rsk_descr]
>
> ,[rsk_sort_order]
>
> ,[equity_deal]
>
> ,[gilt_deal]
>
> ,[bond_deal]
>
> ,[with_charge]
>
> ,[wdl_stock_exception]
>
> ,[check_cgt]
>
> ,[legacy]
>
> ,[model]
>
> ,[portfoliotypeid]
>
> ,[rsk_transferred]
>
> ,[rsk_target]
> FROM [LIVE].[Live_db].[owner].[risks]
>
> ** I changed some names for security reasons.
>
> The baffling thing is that SQL Server can see the tables, create a simple
> query to grab the data but when it tries to access the data it falls over.
>
> ===============================================================
>
> MUCH!
>
> the ANSI erro 42000 and Informix error -201 indicate a syntax error. Can
> you post the query that caused this, maybe we'll see something. I haven't
> done any linked server queries with MS SQL Server myself, and don't
> generally do OLE, .NET or other PC protocols, but I know it all should
> work.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Wed, Apr 21, 2010 at 9:32 AM, JAMES WHITBY <
> jameswhitby@manorhousecottage.com> wrote:
>
> > Better?
> >
> > MESSAGE 2:
> >
> > Yes, these were all done by our Informix DBA on all databases.
> >
> > Can I also add that I have used the Object Explorer Details tool in SQL
> > Server
> > Management Studio, viewed the listed tables and got it to generate a
> select
> > script for me to use to be on the safe side.
> >
> > When running this I get the following message which leads me to believe
> the
> > problem is on the Informix side:
> >
> > OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message
> > "E42000:
> > (-201) A syntax error has occurred.".
> >
> > Msg 7306, Level 16, State 2, Line 1
> >
> > Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
> > "Ifxoledbc" for linked server "LIVE". The specified table or view does
> not
> > exist or contains errors.
> >
> > REPLY 1:
> >
> > Did you install the OLE interface tables? You need to run the SQL script
> > coledbp.sql to do that. See this link:
> >
> >
> >
> >
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
> >
> > Art
> >
> > MESSAGE 1:
> >
> > 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
> >
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--000e0cd23b9a5f4ba40484d0af8a
Art,
It makes no difference whether they are there on not. That is just SQL Server
being overly cautious and expecting spaces in the column names.
When running the query without them, I get the same error message as in my
original post
Cheers
Jim
==========================================
Are all of those square brackets really there? If so then maybe that's your
syntax error, because it's certainly not part of standard ANSI SQL which is
what Informix understands! I know that the OLE and .NET providers do some
simple syntax mapping from MS SQL Server paradigms to standard, but I don't
think that removing all those extraneous brackets is among those mappings.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Apr 22, 2010 at 3:46 AM, JAMES WHITBY <
jameswhitby@manorhousecottage.com> wrote:
> Art,
>
> The query I'm using was auto generated by SQL Servers Object Explorer
> Details
> tool, it found the tables on informix, created a select statement but when
> it
> ran it threw an error!!!
>
> The query was simply:
>
> SELECT [rsk_code]
>
> ,[rsk_descr]
>
> ,[rsk_sort_order]
>
> ,[equity_deal]
>
> ,[gilt_deal]
>
> ,[bond_deal]
>
> ,[with_charge]
>
> ,[wdl_stock_exception]
>
> ,[check_cgt]
>
> ,[legacy]
>
> ,[model]
>
> ,[portfoliotypeid]
>
> ,[rsk_transferred]
>
> ,[rsk_target]
> FROM [LIVE].[Live_db].[owner].[risks]
>
> ** I changed some names for security reasons.
>
> The baffling thing is that SQL Server can see the tables, create a simple
> query to grab the data but when it tries to access the data it falls over.
>
> ===============================================================
>
> MUCH!
>
> the ANSI erro 42000 and Informix error -201 indicate a syntax error. Can
> you post the query that caused this, maybe we'll see something. I haven't
> done any linked server queries with MS SQL Server myself, and don't
> generally do OLE, .NET or other PC protocols, but I know it all should
> work.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Wed, Apr 21, 2010 at 9:32 AM, JAMES WHITBY <
> jameswhitby@manorhousecottage.com> wrote:
>
> > Better?
> >
> > MESSAGE 2:
> >
> > Yes, these were all done by our Informix DBA on all databases.
> >
> > Can I also add that I have used the Object Explorer Details tool in SQL
> > Server
> > Management Studio, viewed the listed tables and got it to generate a
> select
> > script for me to use to be on the safe side.
> >
> > When running this I get the following message which leads me to believe
> the
> > problem is on the Informix side:
> >
> > OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message
> > "E42000:
> > (-201) A syntax error has occurred.".
> >
> > Msg 7306, Level 16, State 2, Line 1
> >
> > Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
> > "Ifxoledbc" for linked server "LIVE". The specified table or view does
> not
> > exist or contains errors.
> >
> > REPLY 1:
> >
> > Did you install the OLE interface tables? You need to run the SQL script
> > coledbp.sql to do that. See this link:
> >
> >
> >
> >
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
> >
> > Art
> >
> > MESSAGE 1:
> >
> > 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
> >
I've only had a little exposure to Openquery, but if that total SQL statement
is being passed to Informix, I'm thinking IDS would have a problem with the
'FROM' line?
IDS uses a 'instance@server.owner.table' syntax?
Bob
----- Original Message -----
From: "JAMES WHITBY" <jameswhitby@manorhousecottage.com>
To: ids@iiug.org
Sent: Thursday, April 22, 2010 6:50:53 AM GMT -05:00 US/Canada Eastern
Subject: Re: Linked Server problems with SQL Server 2008. [19786]
Art,
It makes no difference whether they are there on not. That is just SQL Server
being overly cautious and expecting spaces in the column names.
When running the query without them, I get the same error message as in my
original post
Cheers
Jim
==========================================
Are all of those square brackets really there? If so then maybe that's your
syntax error, because it's certainly not part of standard ANSI SQL which is
what Informix understands! I know that the OLE and .NET providers do some
simple syntax mapping from MS SQL Server paradigms to standard, but I don't
think that removing all those extraneous brackets is among those mappings.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Apr 22, 2010 at 3:46 AM, JAMES WHITBY <
jameswhitby@manorhousecottage.com> wrote:
> Art,
>
> The query I'm using was auto generated by SQL Servers Object Explorer
> Details
> tool, it found the tables on informix, created a select statement but when
> it
> ran it threw an error!!!
>
> The query was simply:
>
> SELECT [rsk_code]
>
> ,[rsk_descr]
>
> ,[rsk_sort_order]
>
> ,[equity_deal]
>
> ,[gilt_deal]
>
> ,[bond_deal]
>
> ,[with_charge]
>
> ,[wdl_stock_exception]
>
> ,[check_cgt]
>
> ,[legacy]
>
> ,[model]
>
> ,[portfoliotypeid]
>
> ,[rsk_transferred]
>
> ,[rsk_target]
> FROM [LIVE].[Live_db].[owner].[risks]
>
> ** I changed some names for security reasons.
>
> The baffling thing is that SQL Server can see the tables, create a simple
> query to grab the data but when it tries to access the data it falls over.
>
> ===============================================================
>
> MUCH!
>
> the ANSI erro 42000 and Informix error -201 indicate a syntax error. Can
> you post the query that caused this, maybe we'll see something. I haven't
> done any linked server queries with MS SQL Server myself, and don't
> generally do OLE, .NET or other PC protocols, but I know it all should
> work.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Wed, Apr 21, 2010 at 9:32 AM, JAMES WHITBY <
> jameswhitby@manorhousecottage.com> wrote:
>
> > Better?
> >
> > MESSAGE 2:
> >
> > Yes, these were all done by our Informix DBA on all databases.
> >
> > Can I also add that I have used the Object Explorer Details tool in SQL
> > Server
> > Management Studio, viewed the listed tables and got it to generate a
> select
> > script for me to use to be on the safe side.
> >
> > When running this I get the following message which leads me to believe
> the
> > problem is on the Informix side:
> >
> > OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message
> > "E42000:
> > (-201) A syntax error has occurred.".
> >
> > Msg 7306, Level 16, State 2, Line 1
> >
> > Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
> > "Ifxoledbc" for linked server "LIVE". The specified table or view does
> not
> > exist or contains errors.
> >
> > REPLY 1:
> >
> > Did you install the OLE interface tables? You need to run the SQL script
> > coledbp.sql to do that. See this link:
> >
> >
> >
> >
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
> >
> > Art
> >
> > MESSAGE 1:
> >
> > 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
> >
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Actually <owner>.<database>@<instance>:<owner>.<table>
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Thu, Apr 22, 2010 at 11:00 AM, rroussey@comcast.net <rroussey@comcast.net
> wrote:
> I've only had a little exposure to Openquery, but if that total SQL
> statement
> is being passed to Informix, I'm thinking IDS would have a problem with the
> 'FROM' line?
> IDS uses a 'instance@server.owner.table' syntax?
>
> Bob
>
> ----- Original Message -----
> From: "JAMES WHITBY" <jameswhitby@manorhousecottage.com>
> To: ids@iiug.org
> Sent: Thursday, April 22, 2010 6:50:53 AM GMT -05:00 US/Canada Eastern
> Subject: Re: Linked Server problems with SQL Server 2008. [19786]
>
> Art,
>
> It makes no difference whether they are there on not. That is just SQL
> Server
> being overly cautious and expecting spaces in the column names.
>
> When running the query without them, I get the same error message as in my
> original post
>
> Cheers
>
> Jim
>
> ==========================================
>
> Are all of those square brackets really there? If so then maybe that's your
> syntax error, because it's certainly not part of standard ANSI SQL which is
> what Informix understands! I know that the OLE and .NET providers do some
> simple syntax mapping from MS SQL Server paradigms to standard, but I don't
> think that removing all those extraneous brackets is among those mappings.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> IIUG Board of Directors (art@iiug.org)
>
> See you at the 2010 IIUG Informix Conference
> April 25-28, 2010
> Overland Park (Kansas City), KS
> www.iiug.org/conf
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Thu, Apr 22, 2010 at 3:46 AM, JAMES WHITBY <
> jameswhitby@manorhousecottage.com> wrote:
>
> > Art,
> >
> > The query I'm using was auto generated by SQL Servers Object Explorer
> > Details
> > tool, it found the tables on informix, created a select statement but
> when
> > it
> > ran it threw an error!!!
> >
> > The query was simply:
> >
> > SELECT [rsk_code]
> >
> > ,[rsk_descr]
> >
> > ,[rsk_sort_order]
> >
> > ,[equity_deal]
> >
> > ,[gilt_deal]
> >
> > ,[bond_deal]
> >
> > ,[with_charge]
> >
> > ,[wdl_stock_exception]
> >
> > ,[check_cgt]
> >
> > ,[legacy]
> >
> > ,[model]
> >
> > ,[portfoliotypeid]
> >
> > ,[rsk_transferred]
> >
> > ,[rsk_target]
> > FROM [LIVE].[Live_db].[owner].[risks]
> >
> > ** I changed some names for security reasons.
> >
> > The baffling thing is that SQL Server can see the tables, create a simple
> > query to grab the data but when it tries to access the data it falls
> over.
> >
> > ===============================================================
> >
> > MUCH!
> >
> > the ANSI erro 42000 and Informix error -201 indicate a syntax error. Can
> > you post the query that caused this, maybe we'll see something. I haven't
> > done any linked server queries with MS SQL Server myself, and don't
> > generally do OLE, .NET or other PC protocols, but I know it all should
> > work.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > See you at the 2010 IIUG Informix Conference
> > April 25-28, 2010
> > Overland Park (Kansas City), KS
> > www.iiug.org/conf
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other
> > organization with which I am associated either explicitly, implicitly, or
> > by
> > inference. Neither do those opinions reflect those of other individuals
> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Wed, Apr 21, 2010 at 9:32 AM, JAMES WHITBY <
> > jameswhitby@manorhousecottage.com> wrote:
> >
> > > Better?
> > >
> > > MESSAGE 2:
> > >
> > > Yes, these were all done by our Informix DBA on all databases.
> > >
> > > Can I also add that I have used the Object Explorer Details tool in SQL
> > > Server
> > > Management Studio, viewed the listed tables and got it to generate a
> > select
> > > script for me to use to be on the safe side.
> > >
> > > When running this I get the following message which leads me to believe
> > the
> > > problem is on the Informix side:
> > >
> > > OLE DB provider "Ifxoledbc" for linked server "LIVE" returned message
> > > "E42000:
> > > (-201) A syntax error has occurred.".
> > >
> > > Msg 7306, Level 16, State 2, Line 1
> > >
> > > Cannot open the table ""live_db":"owner"."rates"" from OLE DB provider
> > > "Ifxoledbc" for linked server "LIVE". The specified table or view does
> > not
> > > exist or contains errors.
> > >
> > > REPLY 1:
> > >
> > > Did you install the OLE interface tables? You need to run the SQL
> script
> > > coledbp.sql to do that. See this link:
> > >
> > >
> > >
> > >
> >
> >
>
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ol
edb.doc/oledb20.htm
> > >
> > > Art
> > >
> > > MESSAGE 1:
> > >
> > > 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@@DQ@
I just set up a linked server from one of my SQL Server 2008 servers to one of
our Informix instances. I ran the coledbp.sql on the Informix side before
initiating a query. I used the SSMS to generate the query I ran, which was
effectively a select * from the table - and it ran fine. To be sure I edited
out all of the explicitly listed column names and ran select * - it also ran
fine. The select statement looked like this:
SELECT * FROM [PROD_POLICY].[policy2009].[informix].[sbi]GO
PROD_POLICY = My linked server name
policy2009 = The Informix database name
informix = The name of the SQL Server 2008 schema that "owns" the table
sbi = The table name of the Informix database policy2009.
To be safe - check your Linked Server's properties on the SQL side,
specifically the Server Options tab. W/o going into great detail - mine are
set in this order (excluding setting names for brevity):
False, True, False, False, True, BLANK, 0, 0, False, False, False, False, True.
Hope that helps.
MM
Do any of the columns in the table you are querying defined as text or byte columns? Maybe try to select data from a single column to see if that works. MM
Also - can you try using openquery? I.e:
select * from openquery(PROD_POLICY, 'select * from informix.fund')
PROD_POLICY = linked server name
informix.fund = schema.tablename
Good luck
MM
On Thu, Apr 22, 2010 at 08:13, Art Kagel <art.kagel@gmail.com> wrote: > Actually <owner>.<database>@<instance>:<owner>.<table> > I'm pretty sure that the database owner cannot be specified. The syntax of: [database[@server]:][owner.]tablename is correct, I think. > > On Thu, Apr 22, 2010 at 11:00 AM, rroussey@comcast.net < > rroussey@comcast.net > > wrote: > > > I've only had a little exposure to Openquery, but if that total SQL > > statement > > is being passed to Informix, I'm thinking IDS would have a problem with > the > > 'FROM' line? > > IDS uses a 'instance@server.owner.table' syntax? > > > > Bob > > > > ----- Original Message ----- > > From: "JAMES WHITBY" <jameswhitby@manorhousecottage.com> > > To: ids@iiug.org > > Sent: Thursday, April 22, 2010 6:50:53 AM GMT -05:00 US/Canada Eastern > > Subject: Re: Linked Server problems with SQL Server 2008. [19786] > > > > Art, > > > > It makes no difference whether they are there on not. That is just SQL > > Server > > being overly cautious and expecting spaces in the column names. > > > > When running the query without them, I get the same error message as in > my > > original post > > > > Cheers > > > > Jim > > > > ========================================== > > > > Are all of those square brackets really there? If so then maybe that's > your > > syntax error, because it's certainly not part of standard ANSI SQL which > is > > what Informix understands! I know that the OLE and .NET providers do some > > simple syntax mapping from MS SQL Server paradigms to standard, but I > don't > > think that removing all those extraneous brackets is among those > mappings. > > > > > > On Thu, Apr 22, 2010 at 3:46 AM, JAMES WHITBY < > > jameswhitby@manorhousecottage.com> wrote: > > > > > Art, > > > > > > The query I'm using was auto generated by SQL Servers Object Explorer > > > Details > > > tool, it found the tables on informix, created a select statement but > > when > > > it > > > ran it threw an error!!! > > > > > > The query was simply: > > > > > > SELECT [rsk_code] > > > > > > ,[rsk_descr] > > > > > > ,[rsk_sort_order] > > > > > > ,[equity_deal] > > > > > > ,[gilt_deal] > > > > > > ,[bond_deal] > > > > > > ,[with_charge] > > > > > > ,[wdl_stock_exception] > > > > > > ,[check_cgt] > > > > > > ,[legacy] > > > > > > ,[model] > > > > > > ,[portfoliotypeid] > > > > > > ,[rsk_transferred] > > > > > > ,[rsk_target] > > > FROM [LIVE].[Live_db].[owner].[risks] > > > > > > ** I changed some names for security reasons. > > > > > > The baffling thing is that SQL Server can see the tables, create a > simple > > > query to grab the data but when it tries to access the data it falls > > over. > > > > > > =============================================================== > > > > > > MUCH! > > > > > > the ANSI erro 42000 and Informix error -201 indicate a syntax error. > Can > > > you post the query that caused this, maybe we'll see something. I > haven't > > > done any linked server queries with MS SQL Server myself, and don't > > > generally do OLE, .NET or other PC protocols, but I know it all should > > > work. > > > [...] > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. --001636d33a3cc3ca010484f23c4b
Mike,
OpenQuery does work but the problem is the data that it's trying to get at and
the limited nature of using it.
I'd rather have something a little more flexible but it may come down to
biting the bullet and just putting up with OQ for the time being.
Thanks for the help
Jim
=========================================
Also - can you try using openquery? I.e:
select * from openquery(PROD_POLICY, 'select * from informix.fund')
PROD_POLICY = linked server name
informix.fund = schema.tablename
Good luck
MM
Related threads
- Error 206 during insert with ESQL/C
- Re: How can I extract just the last(i.e. most current) entry from