TuboIntegrator ODBC with Synapse SQL Pool: SQL execution error

Post Reply
swissman
Posts: 2
Joined: Fri Jul 05, 2024 5:43 pm
OLAP Product: TM1
Version: 11.8.02300.10
Excel Version: -

TuboIntegrator ODBC with Synapse SQL Pool: SQL execution error

Post by swissman »

Hi everyone
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;"
I setup the ODBC connection as System DSN in the Windows ODBC Data Source Administrator (64 bit) using the ODBC Driver 18 for SQL Server:
  • 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
The TurboIntegrator script is as basic as it can be:

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');
with
  • 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
What have I tried so far to locate the problem?
  • 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

    Code: Select all

    select top 1 * from mydb.myschema.mytable;
    (it works also without the top 1).

    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:

    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>
    
    The referenced Process Error file contains:

    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.
Thank you so much for your very appreciated help!

(OS: Windows Server 2019 Standard)
User avatar
Steve Rowe
Site Admin
Posts: 2431
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TuboIntegrator ODBC with Synapse SQL Pool: SQL execution error

Post by Steve Rowe »

Perhaps try setting all the query values / parameters at runtime in the prolog? The you can be very clear exactly what is taking place at runtime.

These values are read / write so you could asciioutput them at run time as well to make sure that the parameters are exactly what you think they are.

https://www.ibm.com/docs/no/planning-an ... -variables
Technical Director
www.infocat.co.uk
MarenC
Regular Participant
Posts: 371
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TuboIntegrator ODBC with Synapse SQL Pool: SQL execution error

Post by MarenC »

Hi,

Very wild stab in the dark, try putting "SET NOCOUNT ON" before your select statement.

Maren
swissman
Posts: 2
Joined: Fri Jul 05, 2024 5:43 pm
OLAP Product: TM1
Version: 11.8.02300.10
Excel Version: -

Re: TuboIntegrator ODBC with Synapse SQL Pool: SQL execution error

Post by swissman »

MarenC wrote: Tue Jul 09, 2024 12:52 pm Hi,

Very wild stab in the dark, try putting "SET NOCOUNT ON" before your select statement.

Maren
Sometimes it's good to stab in the dark, this helped a lot, thank you so much!
Wim Gielis
MVP
Posts: 3144
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TuboIntegrator ODBC with Synapse SQL Pool: SQL execution error

Post by Wim Gielis »

"SET NOCOUNT ON", the Swiss knife for the swissman ;-)
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply