Product:
Planning Analytics 2.0.9.13 TM1_version=TM1-AW64-ML-RTM-11.0.913.10-0
Microsoft Windows 2019 server
Microsoft SQL Server 2012 Native Client ODBC driver version 11.2.5058.0
Issue:
Can not open a connection to a Microsoft SQL database, via ODBCopen command. You can open the connection in the TM1 datasource tab and read the data, but not open in a ODBCopen command in the prolog tab. When you test the ODBC connection in Windows, it works fine with your SQL login.
You get similar error, when you try a 32 bit ODBC or 64 bit ODBC connection.
In Windows event log you get this error:
Faulting application name: tm1odbcproxy32.exe, version: 0.0.0.0, time stamp: 0x62586410
Faulting module name: tm1odbcproxy32.exe, version: 0.0.0.0, time stamp: 0x62586410
Exception code: 0xc0000005
Fault offset: 0x000058f8
Faulting process id: 0xfc8
Faulting application start time: 0x01d93a5fe5481324
Faulting application path: C:\Program Files\ibm\cognos\tm1_64\bin64\tm1odbcproxy32.exe
Faulting module path: C:\Program Files\ibm\cognos\tm1_64\bin64\tm1odbcproxy32.exe
Report Id: cadfaa7a-2357-4370-9ecf-3c6500029586
Faulting package full name:
Faulting package-relative application ID:
Solution:
The connection needs to use UNICODE. Use ODBCOPENex instead.
Format is: ODBCOPENEx (dataset name, dataset client name, client password, (use-Unicode-interface flag) )
https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=functions-odbcopenex
In TM1 Architect data source tab, you get same error if you uncheck the “use unicode” checkbox.
In prolog enter this code:
#--- debug setup sDEBUGfile = 'debugfile3.txt'; sDEBUGpath = 'c:\temp\'; sDEBUG1 = sDEBUGpath | sDEBUGfile ; #-- setup the database connection sODBCname = 'windows2016'; sUser = 'donald'; sPassword = 'Password!'; #-- open the connection to the database with unicode by adding the 1 parameter ODBCOpenEx(sODBCname, sUser, sPassword,1); #-- create the sql statement - adjust for your test database sSQL = 'TRUNCATE TABLE AdventureWorksLT2019.dbo.Lista'; ASCIIOutput ( sDEBUG1, sSQL, 'check sql syntax'); #-- execute the SQL statement ODBCOutput (sODBCname , ( sSQL) );
You can open the SQL ODBC connection in PROLOG, even when other data sources are in use.
sODBCname = ‘windows2016’; should be the name of the ODBC connection you want to use, it can be case-sensitive.
Please try to change ODBC driver in Windows Control Panel on your Windows Server. A new ODBC driver can help.
More Information:
https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tv-turbointegrator-local-variables
https://www.tm1forum.com/viewtopic.php?t=11978
https://www.tm1forum.com/viewtopic.php?t=10947
https://www.tm1forum.com/viewtopic.php?t=16234
https://quebit.com/askquebit/IBM/how-to-speed-up-tm1-odbcoutput/