ODBC connection to Informix puzzle

Question

I have been trying to extract data on a regular basis from an Informix DB to MS SQL 2005 using ODBC. I have set up a data source System DSN 'CX', then used sp_addlinkedserver, sp_addlinkedsrvlogin, and openquery. But then i get error messages see below.

FYI- I have been able to use MS Access 2003 to connect via the same DSN to extract data and link tables.

Code to Link Server:

EXEC sp_addlinkedserver

@server = 'uccx',

@srvproduct= '',

@provider = 'MSDASQL',

@datasrc= 'CX',

@provstr = 'HOST=LIFEBOOK; SERVER=uccx; Protocol=onsoctcp; Database=db_cra;UID=user; PWD=pa;'

GO

EXEC sp_addlinkedsrvlogin 'uccx', 'False'

GO

Connection is shown as LINKED.... but when i run the Query..

FROM OPENQUERY (uccx, 'SELECT * FROM Informix_contactcalldetail')

I receive these Error messages...

OLE DB provider "MSDASQL" for linked server "uccx" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

OLE DB provider "MSDASQL" for linked server "uccx" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

OLE DB provider "MSDASQL" for linked server "uccx" returned message "[Informix][Informix ODBC Driver][Informix]Attempt to connect to database server (uccx) failed.".

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "MSDASQL" for linked server "uccx" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "uccx".

 

I have tried OPENROWSET to directly connect but i receive similar Authentication failed messages. As i mentioned, the puzzle is that MS Access ODBC works fine.

Thank you in advance for any help anyone can provide.

Ray

Answer 1

Hi Ray.

Linked servers to Informix should use OLEDB, not ODBC, but you must run a script $INFORMIXDIR/etc/coledbp.sql on the DB server first, which creates some necessary additional system catalog tables. Contact me directly if I can help further.

Regards,
Doug Lawry
Oninit Consulting Ltd

[e-mail]
www.oninitgroup.co.uk

Answer 2

Hi Doug,

 

Cisco will not let me run the script without warranty violations.  At the same time, they will not help me get my call data out of the system!!!

Luckily, this is still in test and i have a little more time.

Cisco says ODBC is supported for Informix 11, so i still hunting for a solution.

Thanks

Ray

Answer 3

Please let me know if you find a solution to this problem. I have the exact same issue with Cisco Contact Center which has an Informix DB. They will not run the script to support oledb and I can not get a SQL Server 2008R2 ODBC linked server to work. Excel and Access work ok as in your case. Man this is frustrating.

 

[e-mail]

---

Gary Mercier

Answer 4

Hi All,

few months after you, I have exactly the same issue....did you find a solution?

Thanks in advance

Laurent

Answer 5

hi, please check out the below link.

http://www.connectionstrings.com/informix