[QODBC ALL] How to Enable SQL Server Support for OpenQuery Linked Server Inserts with QODBC

Problem Description:

When using INSERT OPENQUERY() to write data through a linked server to QODBC (e.g., from SSMS or another SQL Server instance), the operation may fail with an error such as:

The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "XXX" does not support the required transaction interface.

Affected products: QODBC Desktop, QuickBooks Desktop, MS SQL Server
Expected outcome: After enabling the "SQL Server Support" option in both 32-bit and 64-bit QODBC DSN Advanced settings, INSERT OPENQUERY() operations work correctly.

Symptoms:

• INSERT OPENQUERY Fails with an OLE DB provider "MSDASQL" transaction interface error.
• SELECT queries through the linked server may work, but INSERT/UPDATE/DELETE operations fail.
• The issue is resolved by enabling the SQL Server Support option in QODBC DSN configuration.

Cause:

By default, the QODBC DSN does not have the "SQL Server Support" feature enabled. This feature is required for write operations (INSERT, UPDATE, DELETE) through SQL Server linked servers using OPENQUERY. Without it, the MSDASQL provider cannot establish the necessary transaction interface to perform writes against the QODBC data source.

Environment:

Solution:

Step 1 - Enable SQL Server Support in the 32-bit DSN

1. Open QODBC Setup (32-bit): Start → All Programs → QODBC Driver for QuickBooks → Configure 32-Bit QODBC Driver.
2. Select System DSN and highlight "QuickBooks Data", then click Configure.
3. Switch to the Advanced tab.
4. Check the box for "SQL Server Support" (or "Enable SQL Server Support").
5. Click OK to save the changes.

Step 2 - Enable SQL Server Support in the 64-bit DSN

1. Open QODBC Setup (64-bit): Start → All Programs → QODBC Driver for QuickBooks → Configure 64-Bit QODBC Driver.
2. Select System DSN and highlight "QuickBooks Data", then click Configure.
3. Switch to the Advanced tab.
4. Check the box for "SQL Server Support."
5. Click OK to save the changes.

Step 3 - Test the Linked Server Insert

After enabling SQL Server Support in both DSNs, retry your INSERT OPENQUERY statement:

INSERT OPENQUERY ("DSN_NAME", 'SELECT "FirstName", "LastName", "Name" FROM Vendor')
VALUES ('TEST SQL', '', 'INSERT', 'TEST SQL INSERT')

Additional Notes:

• 32-bit and 64-bit both need it: SQL Server Support must be enabled in both the 32-bit and 64-bit QODBC DSN configurations for write operations to work reliably.
• MSDASQL linked server configuration: If you continue experiencing issues after enabling SQL Server Support, verify that the MSDASQL provider's linked server properties are configured correctly. See Microsoft documentation on configuring MSDASQL for a linked server.
• Always use the latest QODBC version: Ensure you are running the most recent QODBC build to benefit from bug fixes and compatibility improvements.
• Read-only queries don't require it: If you only need to SELECT data through a linked server, SQL Server Support is not required - only write operations (INSERT/UPDATE/DELETE) need this setting enabled.

Related Articles:

How to Activate QODBC Troubleshooting - MSDASQL Linked Server Configuration QODBC - Enabling Dirty Reads for Missing/Null Values

Keywords: