Tm1 9.4MR1 - ODBC driver confused - Resolved

[Olivier]

Hi All,

I have upgraded my Tm1 version from 9.1 sp3 (64bits) to 9.4MR1 FP3.
Server OS is Windows server 2003 64bits.

I realised our ODBC connections to SQL servers have an issue.

The existing ODBC connections were not modified and are defined using the 64bit interface ( Windows/Syswow64/odbcad.exe )

1 - When i test the ODBC connections (using the same account that is used to run the Tm1 install / runs the tm1 services) throught this interface, i can connect succesfully to my sources.
2 - When i test the ODBC connection (using the same account that is used to run the Tm1 install / runs the tm1 services) throught Excel using external data sources, the configurated ODBC connections appears and enable to issue SQL query to the sources.
3 - When i test the connection (using the same account that is used to run the Tm install / runs the tm1 services) throught Tm1 TI the first instance i had the message "umable to connect to database".

4 - When i try to browse to the existing ODBC datasources, the list is blank ( only a back up exec catalog is in the list).

After investigation, I finnally realised that TI was using the list of ODBC link provided from the 32bits ODBC driver configuration ( Windows/System32/odbcad.exe ).
No ODBC links were configured using 32bits so it explain why the list of ODBC sources for TI was blank.

5 - I tryed to configure to ODBC links in the 32 bit interface in order to test if TI would be able to use that.

I can browse to a source configurated using the 32bits interface but now i get the error "SQL statements fails".
( I suppose this is due to the fact that i am using a 64bit version of Tm1 and therefore should not use 32bits ODBC ).

The Tm1 Log file show the following message :
1280 ERROR 2011-07-11 23:01:49.961 TM1.SQLAPI
E16) Cannot connect to ODBC data source "triprd" IM002[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
1280 ERROR 2011-07-11 23:01:49.961 TM1.SQLAPI
08003[Microsoft][ODBC Driver Manager] Connection not open

Note :
In my Dev environment the test TM1 server has been installed exactly the same method, refers to ODBC connections 32bits and is able to connect and issue the sql statements.
The windows account used to run and install the test server is different from the account that is used to run our productiono envronment describe above.
I suspected security setting of the account used in Production but i think it that was the case i could not query the SQL servers using excel in the prod server as described above.

Could somebody confirm ( i read the FAQs) that TM1 64bits versions installed on 64bits OS has to use the ODBC set up using the 64bit interface ?

I would like to understand why TM1 did not use the 64 bits set of ODBC connections by default but instead try to get the list from the 32bits interface ?

Also has anybody an idea how to "connect" TI to the ODBC sources defined in the 64bit interface for ODBC connections ?
( Is that the pupose of the new items of Tm1 config file relating to ODBC ?)

Is account security a cause for "SQL Statement failed" ( Account as in Tm1 service account ) ?


Any ideas on how to investigate this further would be appreciated.

Thanks,

[Andy Key]

I'm afraid you have fallen into Microdaft's trap.

System32 contains the 64-bit files.

SysWoW64 contains the 32-bit files.

I mean, how could anyone get confused by that?

The reasoning is that as so many programs had the System32 path hardcoded within them they took the easy way out and left it. Even leaving files such as odbcad32.exe with the 32 in the file name, even though they are 64 bit programs.

WoW64 is short for Windows on Windows64, i.e. it is the 32-bit Windows subsystem within 64-bit Windows. Microsoft (mostly) still like to keep to the 8.3 naming convention, so didn't call it SysW32onW64, which would have made more sense.

[Olivier]

Thanks for the insight Andy !

I fixed my issue in parrallel by setting up my ODBC connection using the ODBC configuration sitting in System32 ( which as you indicated contains the 64-bit ODBC drivers set up).

I had to specify a bit more parameters in there such as the default database to resolve the "SQL Statement failed" in order to enable TI to find the relevant table/view through the SQl statement...

I was very glad to read your post as my TIs now connect to the ODBC sources but i did not have a clue of WHY

Another very valuable lesson learnt on the Olap forum !

Thanks a lot !