IBM Informix Ole Db Provider

Hello,

I'm getting the below errors when using sql server's data import wizard. Please Note: My test connection was successful and the error occurs when I click the "Next" button after closing the properties window. For Security reasons I've filled the User Id and DataSource with a bogus account, database, and server i.e. xxxx. Notice the password is blank?... Is that normal? I'm in a jam and appreciate your help.

Thanks,

Matt

===================================

Cannot get the supported data types from the database connection "Provider=Ifxoledbc;Password=;Persist Security Info=True;User ID=xxx;Data Source=xxx@.xxxx". (SQL Server Import and Export Wizard)

===================================

IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002).
IErrorInfo.GetDescription failed with E_NOINTERFACE(0x80004002). (System.Data)

Program Location:

at System.Data.OleDb.OleDbConnectionInternal.GetSchemaRowset(Guid schema, Object[] restrictions)
at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.LoadTypeData(OleDbConnection myDestConnection)
at Microsoft.SqlServer.Dts.DtsWizard.DataSourceInfo.CacheDataSourceInfo(IDbConnection myConnection)

Moving to the "SQL Server Integration Services", which deals with data transformation issues.

|||

This means your OLE DB provider does not publish Provider_Types schema. The wizard relies on this schema to map data types between heterogeneous data sources.

You may want to try to build your SSIS package manually using OLE DB Source to access your Informix database.

Thanks.

|||

Hello,

Does your solution apply to the linked server as well? Any chance you know if SQL Server 2005 allows setting up an informix 7.3 db as a Linked Server? I now can connect with ADO.NET 2.0 using the IBM.Data.Informix namespace but have not been able to find any documentation on setting up connection through a linked server. FYI, I now have to correct IBM SDKs installed (2.9).

Thanks,

Matt

|||

I am having the same problem. Is there a way to get SQL server 2005 to talk to the Informix 7.3 engine? I get the following error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Ifxoledbc" for linked server "MINNYME2" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "MINNYME2".

|||

You are in luck. I'm currently still looking into this but have done a significant grind and configured the connection with ODBC. How are you connecting? With the Informix OLEDB? I'm currently waiting for the AIX admin to set something up on the server. He has to run a few scripts to set up the OLEDB properly. I found a page on IBM's site with significant documentation. Please see below. I believe I know what is wrong with my OLEDB connection, it is the set-up. I have a working ODBC now and can help you with that if need be.

http://www-306.ibm.com/software/data/informix/pubs/library/csdk_29.html

Regards,

Matt

|||

Yes, I am connecting using Informix OLE DB. I tried create a linked server with SQL Server 2005 with the IBM Informix OLE DB Provider. Here is my connection info. Did specify something wrong? It seems you abandoned this approach. Should I do the same and try to link the server with ODBC?

/****** Object: LinkedServer [MINNYME5] Script Date: 05/02/2006 11:10:06 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'MINNYME5', @.srvproduct=N'Informix-CLI 2.9 (32 bit)', @.provider=N'Ifxoledbc', @.datasrc=N'bsco@.becton_tcp', @.provstr=N'server=bsco@.becton_tcp;database=bsco;uid=informix;password=tewwetca'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'MINNYME5', @.optname=N'use remote collation', @.optvalue=N'true'

|||

Here is the code to set up the ODBC linked server. Please read the comments and notes provided throughout. I can help you further if need be. Just so we are on the same page, I'm using

OS: Windows XP Pro.

DotNet Framework: .NET SDK 1.1

IBM software development kit version: IBM CSDK 2.9

Good Luck!

EXEC sp_addlinkedserver

@.server = 'yourServer', -- defined in SetNet32 on tab 'Server information', -- field 'Informix Server'

@.provider = 'MSDASQL', -- DO NOT CHANGE !

@.datasrc = 'yourOdbcConnectionName', -- name of the ODBC connection defined in your system. (you must configure this.)

@.srvproduct = 'Informix-CLI 3.30 (32 bit)',

@.provstr = 'Host=yourHost;Server=yourServer;Service=yourService;Protocol=olsoctcp;Database=yourDB;UID=yourUIDToConnectToAIX;PWD=yourPWD'

NOTE: I'm not 100% sure if a login is needed but i created one.

EXEC sp_addlinkedsrvlogin 'yourLinkedServer', 'false', 'yourWindowsXPUserID', 'yourUIDToConnectToAIX', 'yourPassword'

READ-You need to set up permission for the UserId connecting to the AIX server in the AIX server. I had a AIX admin set me up considering I know little about IBM products.

Run the following query from SQL SERVER to query an ODBC linked server:

SELECT * FROM OPENQUERY(yourLinkedServer, 'select * from yourTable')

|||i done that , but cant insert into linked server tables and this error happens
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
[OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
do u know why?

|||

Although I have never tried inserting a row into an Informix Linked Server, I'd assume that it is not possible considering I've seen similar results with other data sources.

|||thank you for replying
also i cant set linked server with oledb informix
when setup linked server with odbc can update, delete and select from linked table but only cant insert into the table