Reputation: 123
We are using Microstrategy to connect to Google Bigquery. The Microstrategy report is issuing Insert
statements to bigquery which are failing with the following error message:
Push Auto Commit failed.
Error type: Odbc error. Odbc operation attempted: SQLSetConnectOption. Option: SQL_ATTR_AUTOCOMMIT. [HYC00:11470: on SQLHANDLE] [MicroStrategy][ODBC] (11470) Transactions are not supported.
Upvotes: 0
Views: 1809
Reputation: 3448
First, download and configure the latest ODBC driver from here:
Next - use the connection like this (note the IgnoreTransactions parameter):
import pyodbc
import pandas as pd
conn = pyodbc.connect(r'Driver={Simba ODBC Driver for Google BigQuery};OAuthMechanism=0;Catalog=<projectID>;KeyFilePath=<path to json credentials>;Email=<email of service account>;IgnoreTransactions=1')
qry = 'select * from <path to your table>'
data = pd.read_sql(qry,conn)
Upvotes: 1
Reputation: 3642
I see two possible options on how to solve this issue:
Option 1
Per Simba ODBC release notes which are found in this link you need to make sure you are using version 2.1.16 and that you have enabled the IgnoreTransactions configuration option
[GAUSS-634] Ignoring Transaction Data
The driver can now be configured to ignore transaction data in query results. To do this, enable the IgnoreTransactions configuration option. For details, see the Installation and Configuration Guide.
Another interesting option in this version
[GAUSS-663] Support for MERGE
The driver now supports MERGE as a DML statement.
Check the Advanced Options in the ODBC SIMBA configuration menu
Option 2
If you are connected to Microstrategy via Linked connection you need to follow the instructions in Simba FAQ page
Insert, Update, and Delete fail with an error saying the Linked Server doesn’t support the required transaction interfaces.
Linked Server does not use ODBC natively, it uses OLE DB and to talk to ODBC it uses the Microsoft OLE DB to ODBC bridge. This is where the problem arises. You can work around this by disabling transactions for DML queries with the provider. Delete the linked server connection to the data source, then open the Linked Server Providers and right click on the MDASQL one. Select the “Non-transacted updates” and then recreate the linked server and try again.
Upvotes: 1