MII 12.2 on Win 2008R2 x64 connecting to ODBC x32 datasources

[former_member202228]

I'm having the same problem as Harold had a few years back ( ), trying various ways to overcome it.

1st approach was to use the sun.jdbc.odbc.JdbcOdbcDriver bridge. As in the former post, the MII defaults to the 64 bit ODBC Manager and the 32 bit data sources can't be seen. The only result I was able to get out of the system is as follows:

Unable to fill connection pool for jdbc:odbc:RadPa

[EXCEPTION]

java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6965)

Long story short, I've installed PCo 2.1 SP04 trying to use OLEDB-ODBC agents. Creating a Source system, I'm selecting OLE-DB agent, in the DataLink properties I'm selecting "Microsoft OLE DB provider for ODBC drivers". Clicking "Next" button I'm able to select the 32 bit system datasource by name from the dropdown.

Clicking the "Test Connection" in the same tab, tells me that it suceeded.

However, the agent does not start, with the following error in the Windows log : "Failed to create ConnectivityAgentProxy : The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc)."

Why is the ODBC driver in the list if it cannot be used with the OLEDB .NET data provider? How do I connect to an ODBC system DSN? There is no other ODBC related item in the PCo list, to play with. Since I'm trying to connect to "defunct" technologies (Foxboro AIM Historian) there are no 64 bt java drivers, no 64 bit ODBC drivers and I need to keep PCo in the loop. All that comes with the AIM client installed on the MII Win 2008 server is a Windows 32 bit ODBC driver. At least this is what the site personnel tells me.

What do I have to try next?

[Former Member · Accepted solution]

Hi Paul,

when you define the ODBC DSN it must be a System DSN. Then you can create the connector in MII as IDBC. I am using

the following parameter and it works fine:

JDBCDriver = sun.jdbc.odbc.JdbcOdbcDriver

ServerURL = jdbc:odbc:<YOUR_DSN_NAME>

With PCo 2.1 SP 6 I get the same error as you.

BR

Pedro Iglesias

[former_member202228 · Accepted solution]

Hi Pedro,

The Datasources are SYSTEM DSN, but 32 bit instead of what MII drivers usually see (64 bit).

In the case of Foxboro AIM Historian, I am not able to create x64 ODBC DSNs (lacking x64 ODBC Provider).

You saved me the time to install, configure and test PCo 2.1 SP06

http://powerprice.info/pics/ODBC32_1.PNG << System DSN Connection, but 32 Bit

http://powerprice.info/pics/ODBC32_2.PNG << ODBC settings

http://powerprice.info/pics/ODBC32_3.PNG << MII 12.2 settings with SUN JDBC-ODBC bridge

Looks like I need to wait for a new driver -- Thanks Maistry! -- .. or forget it

Cheers,

paul.

[Former Member · Accepted solution]

Hi Paul,

My ODBC is a 32 Bit too, using the MS SQL. What happens when you try to connect to a MS SQL via ODBC?

I have no chance to test it with Foxboro.

From your screenshot, I saw that you are using the validation query. Delete this and try again.

BR

Pedro Iglesias

[former_member202228 · Accepted solution]

Hi again Pedro,

Just to have the same test-bed, I'm trying to create a 32 bit ODBC System DSN to the local SQL Server.

Please follow the screenshots below:

http://powerprice.info/pics/ODBC32_4.PNG << System DSN connection x32 to the local MS-SQL (2008 R2)

http://powerprice.info/pics/ODBC32_5.PNG << MII 12.2 settings with SUN JDBC-ODBC bridge

http://powerprice.info/pics/ODBC32_6.PNG << MII 12.2 connector status

http://powerprice.info/pics/ODBC32_7.PNG << MII 12.2 tracelog error

Tough to crack .. here.

[Former Member · Accepted solution]

Hi,

what I can offer you is to have a online session (like webex). One thing more, Please provide the Username and the Password

in your MII Data Connection.

BR

Pedro Iglesias

Edited by: Pedro Iglesias on Feb 1, 2012 8:03 PM

[former_member202228 · Accepted solution]

Adding the user and password .. didn't help. Same output in the tracelog ..

2012 02 01 14:19:09 (..) [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

As much as I would like it, a Webex is not possible due to the enforced security policies and current non-disclosure agreements in place.

Many Thanks,

paul.

[Former Member · Accepted solution]

OK, no problem. From your new screen, I can see that you are using the SQL Server Native Client. I am using the SQL Server and

also with the Native Client is working on my box. On the Authenticity I setup the "With SQL Server authentication using login ID password...". What you have setup?

Why did you take the ODBC driver to connect to MS SQL?

BR

Pedro Iglesias

Edited by: Pedro Iglesias on Feb 1, 2012 8:49 PM

[former_member202228 · Accepted solution]

http://powerprice.info/pics/ODBC32_8.PNG << New System DSN connection x32 to the local MS-SQL (2008 R2) using "SQL Server" ODBC driver

http://powerprice.info/pics/ODBC32_9.PNG << ODBC authentication settings

http://powerprice.info/pics/ODBC32_10.PNG << ODBC link settings

http://powerprice.info/pics/ODBC32_11.PNG << Replicating the MII error using the MS-SQL linked server option to the ODBC souce.

Actually I'm testing the ODBC connectivity to SQL server because you asked me what happens if I try it Same thing as for AIM historian through x32 drivers. From this MII12.2 instance I already have successfull connections to MS-SQL and Oracle using the deployed JDBC regular drivers (jar).

Unfortunately the Foxboro AIM Historian is long dead software, doesn't have a jar driver (not even a 64 bit ODBC driver), hence all the testing here.

paul.

[Former Member · Accepted solution]

Sorry my fault with MS SQL. Long day...

Without to take a look into your system, it is hard to fix it. What about to let connect SAP on your system and check by them?

BR

Pedro Iglesias

[former_member202228 · Accepted solution]

Update:

I have no problems in connecting to the local ODBC x32 datasources by using XMIIUDS40SP02_3-10005680.ZIP selecting the same configuration as the PCo case described above. The Sun JDBC-ODBC bridge does not work with x32 ODBC sources (found some references on the Oracle site).

If the PCo developers will take care of the ODBC driver .. everything will turn out ok.

Paul.

[Former Member]

Paul

We have had a similar problem in the recent past. We are currently on rampup for PCo 2.2 and are working with the development team to develop an ODBC driver (as per your current scenario)