ODBCOutput Delete table error with MS SQL2019

[aboyzhou]

Hi anyone,

I have a strange issue for ODBCOutput to delete table with cretria on MS SQL2019.
I current used PA 2.0.9 TM1-AW64-ML-RTM-11.0.914.2-0

If the data I need to delete exists in the table, I can successfully execute the ODBCOutput sql statement, but if the data I need to delete does not exist in the table, the delete returns an error.

Then error message is :4692 [a] ERROR 2024-03-16 15:44:56.320 TM1.Process 进程 "Import - 1506": ：执行已中止。错误文件：TM1ProcessError_20240316074456_31304692_Import - 1506.log : Error executing SQL query: " DELETE FROM PLAN_BI_DATA_COST WHERE Year = '2024' AND Month = '2'". Error: ""

I did not get any error message from MS SQL2019.
What should I do to avoid this error?

anybody can give some conclusion? I will appreciated.

Thanks a lot.

[gtonkin]

It may be worth enabling the SQL related loggers to see what additional information is available.

[aboyzhou]

Hi, gtonkin,
Thanks a lot for your reference.

But I did not get any valid information, is that mistake with my tm1s-log.properties?

below is my tm1s-log.properties:
log4j.logger.TM1.SQL=DEBUG,ERROR,WARN, SQL
log4j.logger.TM1.SQLAPI=DEBUG,ERROR,WARN, SQL
log4j.additivity.TM1.SQL=false
log4j.additivity.TM1.SQLAPI=false
log4j.appender.SQL=org.apache.log4j.SharedMemoryAppender
log4j.appender.SQL.MemorySize=5 MB
log4j.appender.SQL.File=TM1SQL.log
log4j.appender.SQL.MaxFileSize=50 MB
log4j.appender.SQL.MaxBackupIndex=5
log4j.appender.SQL.Timezone=Local


log4j.logger.TM1.TILogOutput=INFO,ERROR,WARN, LOGOUTPUT
log4j.additivity.TM1.TILogOutput=true
log4j.appender.LOGOUTPUT=org.apache.log4j.SharedMemoryAppender
log4j.appender.LOGOUTPUT.MemorySize=5 MB
log4j.appender.LOGOUTPUT.File=TM1LogOutput.log
log4j.appender.LOGOUTPUT.MaxFileSize=50 MB
log4j.appender.LOGOUTPUT.MaxBackupIndex=5
log4j.appender.LOGOUTPUT.Timezone=Local

And return log as below:
2024-03-16 22:05:51 local Shared memory reader PID 9532 started, for shmem "tm1s.exe-6088_62"
9832 [2] DEBUG 2024-03-16 22:05:50.823 TM1.SQLAPI Initializing SQLAPI with version 3.x
9832 [2] DEBUG 2024-03-16 22:05:50.828 TM1.SQLAPI Executing SQLAllocEnv
9832 [2] DEBUG 2024-03-16 22:05:50.868 TM1.SQLAPI Executing SQLDataSourcesW
9832 [2] DEBUG 2024-03-16 22:05:50.868 TM1.SQLAPI Executing SQLDataSourcesW
9832 [2] DEBUG 2024-03-16 22:05:50.869 TM1.SQLAPI Executing SQLDataSourcesW
9832 [2] DEBUG 2024-03-16 22:05:50.869 TM1.SQLAPI Executing SQLDataSourcesW
9832 [2] DEBUG 2024-03-16 22:05:50.869 TM1.SQLAPI Executing SQLFreeEnv
9832 [2] DEBUG 2024-03-16 22:05:50.873 TM1.SQLAPI Executing SQLAllocHandle
9832 [2] DEBUG 2024-03-16 22:05:50.915 TM1.SQLAPI Executing SQLSetEnvAttr
9832 [2] DEBUG 2024-03-16 22:05:50.915 TM1.SQLAPI Executing SQLAllocHandle
9832 [2] DEBUG 2024-03-16 22:05:50.916 TM1.SQLAPI Executing SQLSetConnectAttrW
9832 [2] DEBUG 2024-03-16 22:05:50.916 TM1.SQLAPI Doing SQLConnectW( "TM12BI", "tm1" )
9832 [2] DEBUG 2024-03-16 22:05:50.916 TM1.SQLAPI Executing SQLConnectW
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLGetDiagField
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLGetDiagRecW
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLGetDiagRecW
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLAllocHandle
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLGetInfo
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLGetInfo
9832 [2] DEBUG 2024-03-16 22:05:50.940 TM1.SQLAPI Executing SQLGetInfo
9832 [2] DEBUG 2024-03-16 22:05:50.941 TM1.SQLAPI Executing SQLGetInfoW
9832 [2] DEBUG 2024-03-16 22:05:50.941 TM1.SQLAPI Executing SQLGetInfoW
9832 [2] DEBUG 2024-03-16 22:05:50.941 TM1.SQLAPI Executing SQLGetInfoW
9832 [2] DEBUG 2024-03-16 22:05:50.941 TM1.SQLAPI Executing SQLGetInfoW
9832 [2] DEBUG 2024-03-16 22:05:50.941 TM1.SQLAPI Executing SQLGetTypeInfo
9832 [2] DEBUG 2024-03-16 22:05:50.942 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.942 TM1.SQLAPI Executing GetBindColInfo
9832 [2] DEBUG 2024-03-16 22:05:50.942 TM1.SQLAPI Executing SQLGetData
9832 [2] DEBUG 2024-03-16 22:05:50.942 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.942 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.942 TM1.SQLAPI Executing SQLGetTypeInfo
9832 [2] DEBUG 2024-03-16 22:05:50.943 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.943 TM1.SQLAPI Executing GetBindColInfo
9832 [2] DEBUG 2024-03-16 22:05:50.943 TM1.SQLAPI Executing SQLGetData
9832 [2] DEBUG 2024-03-16 22:05:50.943 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.943 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.943 TM1.SQLAPI Executing SQLGetTypeInfo
9832 [2] DEBUG 2024-03-16 22:05:50.944 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.944 TM1.SQLAPI Executing SQLGetData
9832 [2] DEBUG 2024-03-16 22:05:50.944 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.944 TM1.SQLAPI Executing SQLGetTypeInfo
9832 [2] DEBUG 2024-03-16 22:05:50.945 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.945 TM1.SQLAPI Executing GetBindColInfo
9832 [2] DEBUG 2024-03-16 22:05:50.945 TM1.SQLAPI Executing SQLGetData
9832 [2] DEBUG 2024-03-16 22:05:50.945 TM1.SQLAPI Executing SQLFetch
9832 [2] DEBUG 2024-03-16 22:05:50.946 TM1.SQLAPI Executing GetBindColInfo
9832 [2] DEBUG 2024-03-16 22:05:50.946 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.946 TM1.SQLAPI Executing SQLGetInfo
9832 [2] DEBUG 2024-03-16 22:05:50.946 TM1.SQLAPI Executing SQLAllocHandle
9832 [2] DEBUG 2024-03-16 22:05:50.946 TM1.SQLAPI Executing SQLExecDirectW
9832 [2] DEBUG 2024-03-16 22:05:50.947 TM1.SQLAPI Executing SQLGetDiagField
9832 [2] DEBUG 2024-03-16 22:05:50.947 TM1.SQLAPI Executing SQLEndTran
9832 [2] DEBUG 2024-03-16 22:05:50.947 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLEndTran
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLFreeStmt
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLFreeHandle
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLAllocHandle
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLFreeHandle
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLFreeHandle
9832 [2] DEBUG 2024-03-16 22:05:50.951 TM1.SQLAPI Executing SQLDisconnect
9832 [2] DEBUG 2024-03-16 22:05:50.952 TM1.SQLAPI Executing SQLFreeHandle
9832 [2] DEBUG 2024-03-16 22:05:50.952 TM1.SQLAPI Executing SQLFreeHandle

What's wrong with my tm1s-log.properties?
Thanks a lot.

Best Regards,

[aboyzhou]

Hi anyone,

I also try to exectue store procedure with TM1, but still did not get any error message.
Can somebody help for this error? or maybe it is a potential Bug with TM1?

But I copy sql query to MS SQL Studio to execute, It returns "line 0 is affected." and there are not any other error message.

below is return log detail:
8948 [2] INFO 2024-03-18 11:33:51.776 TM1.Process Process "Test - Sql test" executed by user "Admin"
8948 [2] ERROR 2024-03-18 11:33:51.914 TM1.Process 进程 "Test - Sql test": ：执行已中止。错误文件：TM1ProcessError_20240318033351_90608948_Test - Sql test.log : Error executing SQL query: " Exec DelTableRecord 'PLAN_BI_DATA_COST','2024','1'". Error: ""
8948 [2] INFO 2024-03-18 11:35:07.665 TM1.Process Process "Test - Sql test" executed by user "Admin"
8948 [2] ERROR 2024-03-18 11:35:07.804 TM1.Process 进程 "Test - Sql test": ：执行已中止。错误文件：TM1ProcessError_20240318033507_79808948_Test - Sql test.log : Error executing SQL query: " DELETE FROM PLAN_BI_DATA_COST WHERE Years = '2024' AND Months = '1'". Error: "..."
8948 [2] INFO 2024-03-18 11:36:09.192 TM1.Process Process "Test - Sql test" executed by user "Admin"
8948 [2] ERROR 2024-03-18 11:36:09.320 TM1.Process 进程 "Test - Sql test": ：执行已中止。错误文件：TM1ProcessError_20240318033609_30908948_Test - Sql test.log : Error executing SQL query: " Exec DelTableRecord 'PLAN_BI_DATA_COST','2024','1'". Error: ""
8948 [2] INFO 2024-03-18 11:40:57.101 TM1.Process Process "Test - Sql test" executed by user "Admin"
8948 [2] ERROR 2024-03-18 11:40:57.254 TM1.Process 进程 "Test - Sql test": ：执行已中止。错误文件：TM1ProcessError_20240318034057_24508948_Test - Sql test.log : Error executing SQL query: " Exec DelTableRecord 'PLAN_BI_DATA_COST','2024','1'". Error: ""

[Wim Gielis]

Hello,

Do you have an environment with the latest version of PA ?

[aboyzhou]

Hello,

Do you have an environment with the latest version of PA ?

Thanks a lot for your reply.
No, we does not use lastest version. The testing environment version is PA2.0.914
Because upgrading to the latest version is a heavy job that involves coordinating multiple departments, we can not do it.

finanlly, I just use sqlcmd to solve this problem. If somebody met same issue who can executeCommand with sqlcmd component in TI.

Best Regards,

[Wim Gielis]

Hello,

I did not mean a production environment, but a testing environment would have been good too.

Nice to see that there is a solution. Just note that ExecuteCommand will not be available anymore in the next major version of PA (that being said, changes to ODBC could come too but that's a different discussion).

[aboyzhou]

Hi Wim,

I will test latest verison on my PC.

Very appreciated your reference.

Best Regards,