ODBC to Informix problem

• rayberry

  SSC Veteran

  Points: 216

  More actions

  September 19, 2011 at 9:04 am

  #244018

  I have been trying to create a stored procedure to extract data on a regular basis from MS SQL 2005 connecting to Informix database using ODBC. On my SQL box 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

• nmourfield@gmail.com

  SSC Veteran

  Points: 222

  More actions

  September 19, 2011 at 9:11 am

  #1383351

  First, 32 bit or 64 bit drivers?

  Second, 32 bit or 64 bit sql server?

  You need to make sure you can run this on the Informix Server

  dbaccesss sysmaster coledbp.sql

  The query method is:

  select * from Openquery(demo_on,'select * from customer');

  Reference

  https://www-304.ibm.com/support/docview.wss?uid=swg21195578

  The first line of code starts with coffee. The last line ends with alcohol.

• rayberry

  SSC Veteran

  Points: 216

  More actions

  September 20, 2011 at 7:50 pm

  #1384290

  Hi Masakatsu,

  I will not be able to run:

  dbaccesss sysmaster coledbp.sql

  due to restrictions to database access by Cisco. This is one of their locked down call center appliances (UCCx 8.5) without voiding support.

  At the same time they say there is OBDC access -- but they will not provide support.

  Thanks for the help, anyway.

  Ray