RE: Linked Server problems with SQL Server 2008.
Try this instead:
SELECT * FROM OPENQUERY (LIVE, 'SELECT * FROM risk')
This assumes that live_db is the default database for your linked server. You
can also do inserts, updates and deletes using openquery.
--EEM
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> JAMES WHITBY
> Sent: Thursday, April 22, 2010 2:47 AM
> To: ids@iiug.org
> Subject: Re: Linked Server problems with SQL Server 2008. [19784]
>
> 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=/c
> om.ibm.oledb.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.