UCCX 10.1 - Linked Server - Copying HR data to external SQL Server for reporting

[John Layton]

I have a customer that is migrating from UCCX 7 to UCCX 10.  They currently have a 2008 SQL Server R2 that they use for custom reports.  They have a package that runs nightly to copy relevant data from UCCX 7 to the their server and then run reports daily against that database.   Pretty easy when everything is in MS SQL.  My plan was to set up a linked server in SQL 2008 to the UCCX server.  This would allow me to easily run a query against Informix and move the data over as before.  However, I am having problems adding the linked server.

I have installed the Informix drivers (both 32 and 64 bit) during the troubleshooting.

The DSN is created and connects to UCCX just fine.

When I attempt to add the linked server using 64 bit driver DSN I get the following:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "UCCX101".
OLE DB provider "MSDASQL" for linked server "UCCX101" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "UCCX101" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "UCCX101" returned message "[Informix][Informix ODBC Driver][-11302] Insufficient Connection information was supplied". (.Net SqlClient Data Provider)

When I attempt to add the linked server using the 32 bit driver DSN I get the following:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "32UCCX101".
OLE DB provider "MSDASQL" for linked server "32UCCX101" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)

It appears to me there is a 32/64 bit conflict no matter route I go.

Can anyone tell me what I am missing?

Is there a better approach to take to achieve the ultimate goal?

Any suggestions are greatly appreciated.

TIA

John

[Gergely Szabo]

Hi,

I have set up a similar mechanism, actually copying information from a UCCX 8.0 database to a Mysql server. I know it's not the most elegant solution but it works alright: it's a Groovy script taking the primary key values from the "offline" server, comparing them to the UCCX "online" tables and everything new is copied over in one batch.

G.

[John Layton]

G.

Thanks for that info. The customer would prefer to keep in SQL using a Job or SSIS if possible.

John

[Elliot Dierksen]

I have created linked servers on an SQL2K box that points back to CCX. First off, you have to set the password for the UCCX HR user to some known value.

exec sp_dropserver 'fonccx9_01', 'droplogins'
;

EXEC sp_addlinkedserver
@server='fonccx9_01', -- Name of the Linked Server, when it is created.
@srvproduct='Microsoft OLE DB Provider for ODBC', -- OLE DB Provider.
@provider='MSDASQL', -- Provider_name.
@datasrc ='fonccx9-01', -- DSN Name of the ODBC Data Source.
@provstr= -- ODBC Connection String.
'Driver={IBM Informix ODBC};Server=fonccx9_01_uccx;Database=db_cra; Uid=uccxhruser; Pwd=Some-Password;'
;

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'fonccx9_01',
    @useself = 'FALSE',
    @rmtuser = 'uccxhruser',
    @rmtpassword = 'Some-Password'
;

Note that the server name is "fonccx9-01", but the dash is an illegal character in the server name, so you have to change that to an underscore. You still have to manually build the ODBC data source on the box where you are trying to create the linked server.

[kdamisch]

John - were you ever able to resolve this?  I am getting the EXACT same 7303 error.  In my case, it is UCCX 11.6.  I've created a DSN and that connects just fine.

Thanks,

Kevin

[paulonwude]

Hi Kevin,

Were you able to fix this? In my case i have the 32bit version of informix client sdk. Can't find the 64bit software on ibm website.

--Paul

[Jim duPuis]

You can get the most recent 64bit drivers from installing Developer Edition from IBM's site. If that link doesn't work, I'm guessing the one on Unity Tool's website will be up to date. You have to either create a free login to their site, or use someone else's. Customize the installation to only install the ODBC drivers. Setup the system dsn, then follow Elliot's instructions. Just did this with 11.6.2 and SQL Server 2017 and it worked like a charm.