MyAlteryx
Community
Academy
Help Docs

Welcome to the Alteryx Knowledge Base

Knowledge Base / Designer / Alteryx Designer: Error: "Operation not allowed during the transaction state" when writing to G...
Alteryx Designer: Error: "Operation not allowed during the transaction state" when writing to Google BigQuery using Output Data tool
user

Created/Edited - 12/6/2024 by Kenneth Low | Alteryx

Summary
Article-7192
Description

The Output Data tool gives the following error when writing to Google BigQuery:

operation not allowed during the transaction state


Reviewing the ODBC DSN trace captured according to this KB article may show the following errors:

ERROR 27228 Simba::BigQuery::GBQApiError::LogError: HTTP error: Not found: Job ... [code: 404][reason: notFound]
ERROR 27228 Simba::ODBC::Connection::BeginTransaction: [Simba][BigQuery] (100) HTTP Error 404: notFound ...


Reviewing the generic ODBC trace captured according to this KB article shows this error:

DIAG [HY000] [Simba][BigQuery] (100) HTTP Error 400: invalidQuery (Transaction control statements are supported only in scripts or sessions)
Issue
When writing to Google BigQuery using an ODBC data connection in the Output Data tool, it gives the error: "Operation not allowed during the transaction state"
Environment
  • Alteryx Designer
    • All Versions
  • Google BigQuery
  • Simba ODBC Driver for Google BigQuery
    • 2.5+
Prerequisites
Output Data tool is configured to use a Google BigQuery ODBC data connection that uses the Simba ODBC Driver for Google BigQuery version 2.5 and newer.
Resolution

Solution A: Check the Enable Session option in the Google BigQuery DSN setup

  1. From the ODBC Data Source Administrator, open the Simba ODBC Driver for Google BigQuery DSN Setup window
  2. Select Advanced Options...
  3. Check the Enable Session option
  4. Click OK and then OK again on the Simba ODBC Driver for Google BigQuery DSN Setup window
  5. Run the workflow again
 

Solution B: Use a Google BigQuery Bulk Connection

Refer to the steps in the following Help documentation to set up the Google BigQuery Bulk Connection: Google BigQuery . The connection would be set up through the Data Connection Manager (DCM) which is available in Designer 2022.3+.
Was this article helpful?