I am trying to setup an import process with TurboIntegrator into TM1 from an Azure Synapse dedicated SQL pool.
When I try to run the process, I get the following error:
Code: Select all
Process error log
Process "MyProcess" aborted with errors
Error: MetaData procedure line (0): Error executing SQL query: "select * from mydb.myschema.mytable;"
- Server: mydomain.sql.azuresynapse.net
- Authentication: SQL Server authentication. I provide username/password in the ODBC DSN configuration and the same credentials again in the PAW process editor.
- Change default database: mydb
- Default settings elsewise
Code: Select all
#Section Prolog
LogOutput('INFO', 'Prolog');
#****Begin: Generated Statements***
#****End: Generated Statements****
LogOutput('INFO', 'Prolog done');
#Section Metadata
LogOutput('INFO', 'Metadata');
#****Begin: Generated Statements***
#****End: Generated Statements****
LogOutput('INFO', 'Metadata done');
#Section Data
LogOutput('INFO', 'Data');
#****Begin: Generated Statements***
#****End: Generated Statements****
LogOutput('INFO', 'Data done');
#Section Epilog
LogOutput('INFO', 'Epilog');
#****Begin: Generated Statements***
#****End: Generated Statements****
LogOutput('INFO', 'Epilog done');
- Data source: Database connection
- Database connection: MyOdbc
- Username (optional): SQL username
- Password (optional): SQL password
- Unicode: yes
- Database connection query: select * from mydb.myschema.mytable;
- No parameters
- Make sure the database is reachable, has data, and the user credentials work. I installed the Microsoft ODCB Test tool (Unicode, amd64), with which I manage to connect to the database and read data. For reference, see the output of (it works also without the top 1).
Code: Select all
select top 1 * from mydb.myschema.mytable;
Code: Select all
dbc: szSqlState = "01000", *pfNativeError = 5701, szErrorMsg = "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed database context to 'mydb'." dbc: szSqlState = "01000", *pfNativeError = 5703, szErrorMsg = "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed language setting to us_english." Full Connect: Full Connect(Default) Successfully connected to DSN 'MyOdbc'. SQLExecDirect: In: Statementhandle = 0x0000000004F30920, StatementText = "select top 1 * from mydb.myschema.mytable;", Statementlength = 42 Return: SQL_SUCCESS=0 Get Data All: "MyColumn1","MyColumn2" "123", 1.000 1 row fetched from 2 columns.
- Removed database or schema name from the query, or simplified the query, i.e. I am getting the same error for each of the following:
Code: Select all
select * from mydb.myschema.mytable; select * from myschema.mytable; select * from mytable; select 1;
- Checked the tm1server.log:
The referenced Process Error file contains:
Code: Select all
90188 [d] INFO 2024-07-06 07:18:58.082 TM1.Process Process "MyProcess" executed by user "SWI" 90188 [d] INFO 2024-07-06 07:18:58.083 TM1.TILogOutput Prolog 90188 [d] INFO 2024-07-06 07:18:58.083 TM1.TILogOutput Prolog done 90188 [d] ERROR 2024-07-06 07:18:58.657 TM1.Process Process "MyProcess": : Execution was aborted. Error file: <TM1ProcessError_20240706071858_65390188_MyProcess.log>
Code: Select all
Error: MetaData procedure line (0): Error executing SQL query: "select * from mydb.myschema.mytable;"
- Consult the Azure logs. When I try to run the process in PAW, I don't see any SQL query, just some successful requests which seem to fetch some meta information or something alike. Interestingly, when I open the process from the TM1 Architect / Server Explorer, I observe a successful execution of select * from mydb.myschema.mytable; which leads to the variables being set up in the variables tab - however, only when opening the process definition dialog from the server explorer, but not when I run the process... (clicking on the preview button gives an error "SQL statement failed")
- Remove all code from the process script, i.e. leave just the section headers and the blocks for the generated code. Then the process runs successfully, but there is no SQL query executed as far as I see in the Azure logs.
(OS: Windows Server 2019 Standard)