Linked Server

I have setup a linked Informix server in SQL7.0 and I am trying to create a simple View with the following SQL statement SELECT doc_code FROM FOURSITE.foursite.informix.watdoc WHERE (order_no = [PV01963B ]) I am getting the following error: --------------------------- SQL Server Enterprise Manager --------------------------- [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema. --------------------------- OK Help --------------------------- Can any one tell me how to correct this problem? Thanks Peter pczurak@bigfoot.com

Peter Czurak wrote: > > I have setup a linked Informix server in SQL7.0 and I am trying to create a > simple View with the following SQL statement > > SELECT doc_code > FROM FOURSITE.foursite.informix.watdoc > WHERE (order_no = [PV01963B ])> > I am getting the following error: > > --------------------------- > SQL Server Enterprise Manager > --------------------------- > [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or > catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but > the provider does not expose the necessary interfaces to use a catalog > and/or schema. > --------------------------- > OK Help > --------------------------- > > Can any one tell me how to correct this problem? > No. What you need to do is get the FROM clause to specify something like: foursite@foursite:"informix".watdoc where the first foursite is the name of the database, the second is the name of the database server (from the Informix sqlhosts file or equivalent), "informix" is the table owner, and watdoc is the table name. How you get that done, I do not know. I'd use DB-Access directly on the Informix database and wouldn't try to have ODBC do the DDL. But I wouldn't be using ODBC on an MS platform either -- if at all possible. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Thanks Jonathan for your help! What do you mean 'DB-Access directly' how are you doing that? Thanks Peter pczurak@bigfoot.com "Jonathan Leffler" <jleffler@informix.com> wrote in message news:3974A290.2AC4E12F@informix.com... > Peter Czurak wrote: > > > > I have setup a linked Informix server in SQL7.0 and I am trying to create a > > simple View with the following SQL statement > > > > SELECT doc_code > > FROM FOURSITE.foursite.informix.watdoc > > WHERE (order_no = [PV01963B ])> > > > I am getting the following error: > > > > --------------------------- > > SQL Server Enterprise Manager > > --------------------------- > > [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or > > catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but > > the provider does not expose the necessary interfaces to use a catalog > > and/or schema. > > --------------------------- > > OK Help > > --------------------------- > > > > Can any one tell me how to correct this problem? > > > > No. What you need to do is get the FROM clause to specify > something like: > > foursite@foursite:"informix".watdoc > > where the first foursite is the name of the database, the second is > the name of the database server (from the Informix sqlhosts file or > equivalent), "informix" is the table owner, and watdoc is the table > name. > > How you get that done, I do not know. I'd use DB-Access directly on > the Informix database and wouldn't try to have ODBC do the DDL. But > I wouldn't be using ODBC on an MS platform either -- if at all possible. > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!"