invalid schema or catalog from MS SQL linked serve

Hi, I have the following databases (Informix = DNS 10.00FC8 on Linux RedHat , MS SQL server= Version 2008 EXPRESS running on Windows 2003R2 SP2, I am using IBM Client version 3.5 TC4 to connect from Windows server to Linux). I setup the Informix database as a linked server on MSSQL using “Microsoft OLE DB provider for ODBC”. It works….but performance is bad, unless I use OPENQYERY from MSSQL like “select * from openquery(select * from mytable)". What could be the reason for the bad performance? So I tried setting up the linked server using “Informix OLE DB Provider” including running coledbp.sql and manually registering the DLL. My connection is OK and OPENQUERY still works perfect. I believe, I should refer to tables in this format Linkedserver..tableowner.table (tried other format too). However I get this error message, when trying something like: 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 don’t know anything about Informix, so I am quit stuck now and would really appreciate some advice. My only option is to use OPENQUEY, which would cause a lot of extra work for me. Thanks! Per

I worked with some folks that were doing BI type reports from production IDS instances to load SQL Server databases. All they ever used was Openquery. They knew a lot more about MS than I do. Bob ----- Original Message ----- From: "PER SORENSEN" <pbs@buussys.dk> To: ids@iiug.org Sent: Friday, December 18, 2009 3:21:58 PM GMT -05:00 US/Canada Eastern Subject: invalid schema or catalog from MS SQL linked serve [18437] Hi, I have the following databases (Informix = DNS 10.00FC8 on Linux RedHat , MS SQL server= Version 2008 EXPRESS running on Windows 2003R2 SP2, I am using IBM Client version 3.5 TC4 to connect from Windows server to Linux). I setup the Informix database as a linked server on MSSQL using Microsoft OLE DB provider for ODBC. It works.but performance is bad, unless I use OPENQYERY from MSSQL like select * from openquery(select * from mytable)". What could be the reason for the bad performance? So I tried setting up the linked server using Informix OLE DB Provider including running coledbp.sql and manually registering the DLL. My connection is OK and OPENQUERY still works perfect. I believe, I should refer to tables in this format Linkedserver..tableowner.table (tried other format too). However I get this error message, when trying something like: 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 dont know anything about Informix, so I am quit stuck now and would really appreciate some advice. My only option is to use OPENQUEY, which would cause a lot of extra work for me. Thanks! Per

Hi Bob, Thanks for your comments! I really hope that I can avoid using OPENQUERY. To make things REALLY strange, then I am able to access one table in informix using the 4 part notation using blank schema and owner (like select * from TEST3..mytable). I tried like 20-30 other tables which all gives me the "An invalid schema or catalog was specified for the provider "Ifxoledbc" for linked server "TEST3"". The DBA (of the Informix database) claims that this table is exactly like any other tables in the database, but something must be different?? Except for the owner? What else could be different? Per