N/APosted on - 05/21/2011
The following databases are running on my computer:
Informix DNS 10.00FC8 with Linux RedHat
MS SQL Version 2008 Express running on my Windows 2003 R2 SP2
And lastly, I am running IBM client Version 3.5 TC4 when connecting with Windows Server to Linux. I configured the Informix and linked the server to SQL with the help of OLE Database for ODBC. It was working but it failed on completion. I need to use the OPENQUERY in SQL to make it work flawlessly. I want to know if it is just fine to and if OPENQUERY is working better than selecting it in the simplest way.
And then, I tried to configure the linked server with Informix OLE DB Provider. The connection and the OPENQUERY went okay. I referred to tables under the Linkedserver..tableowner.table format. But I only get error:
select * from TEST3..informix.mytable;
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "Ifxoledbc" for linked server "TEST3".
I can select the table in Informix with select format on the linkedserver..table but it positively run on one table only.
I can use the OPENQUERY but it will require a lot of job. What can be the solution for this?
Informix Problem: Msg 7313, invalid schema or catalog
You have asked in your queries, if it is normal for an OPENQUERY to work smoother than an ordinary SELECT query based on the set-up you have mentioned. Yes it is true. We don’t have no other better way for that because OPENQUERY works as a pass-through query on the client or remote server which will generate as a result, a linked table on the local server which you can reference for a simple SELECT statement in your local server.
OPENQUERY runs a specific query through the pass-through query on the given linked table of server using Microsoft OLE DB provider for ODBC. This SQL function enables you to reference the FROM statement in a query as the name of the table. This function will also enabled you to reference as an input table, the UPDATE, DELETE or INSERT commands which are also capable on the OLE DB data source. In spite of the truth that the query returns several results of sets, the functions still returns only the first result.
Just like in Microsoft Access, creating two different databases and then linking the two at a later time will require a linked table so that you can access the table on the other table. This is not a good example but we were just showing you some scenario which makes you understand the OPENQUERY. First, we will create a link table linking the other Access Database (this process is the OPENQUERY in your case). Then after creating a link table, we will use the link table as a reference in our new SELECT query (this will be the outside SELECT in your case).