Description
Description

[SQL Server] Error: "Operation not allowed during the transaction state" when writing to Google BigQuery using Output Data tool

inno
2024-09-06 14:55 87 0
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+.

コメント

[SQL Server] 暗号化、復号化 / ENCRYPTBYPASSPHRASE / DECRYPTBYPASSPHRASE
2014-02-28 8421
[SQL Server] 日本カレンダーデータを作成するSQL　（祝日を含む） / Japanese Calendar SQL (Holiday)
2013-11-27 8180
[SQL Server] Error: "Operation not allowed during the transaction state" when w..
2024-09-06 87
[SQL Server] UrlEncode関数
2024-05-24 343
[SQL Server] Unix Timestamp to datetime
2024-02-02 436
[SQL Server] 比率を計算する簡単な方法
2024-01-18 444
[SQL Server] サーバ再起動時にIDENTITYが 1000が増える問題の解決方法
2023-10-18 535
[SQL Server] テーブルのカラムにあるデータをカンマ区切りでTABLE化する。
2023-07-28 524
[SQL Server] A connection was successfully established with the server, but the..
2023-03-30 757
[SQL Server] gacha ガチャ・くじ引き関連SQL
2022-09-07 796
[SQL Server] OpenQueryを利用した insert, update, delete文の使い方
2022-02-07 2168
[SQL Server] 「,」カンマ(COMMA)区切りのデータをテーブル(TABLE)に変換する方法
2018-05-29 1478