SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse

shoin — Wed, 22 Feb 2023 18:58:02 GMT

WIN 2019 9.4 M7 install, uing SAS ACCESS/ODBC with proper GRANTS by Synapse DBA, yet INSERT statement failing using PROC SQL

ERROR: CLI execute error: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]111214;An attempt to complete a transaction has
failed. No corresponding transaction found.

I have the sastrace tuned on, part shared

ODBC: ENTER SQLEndTran
                              2 <SQL_HANDLE_DBC>
             0x000000006cbda3b0
                              1 <SQL_ROLLBACK>
ODBC: EXIT SQLEndTran with return code -1 (SQL_ERROR)
                              2 <SQL_HANDLE_DBC>
             0x000000006cbda3b0
                              1 <SQL_ROLLBACK>
ERROR: CLI execute error: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]111214;An attempt to complete a transaction has 
       failed. No corresponding transaction found.
ODBC: ENTER SQLFreeStmt
             0x000000006cbdb8d0
                              0 <SQL_CLOSE>

TIA for any suggestion. I have also asked the DBA to share the Synpase DB GRANT setting. This particular env is using a service ID to connect and the question to DBA is for GRANT on this service ID.

Re: SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse

SASKiwi — Wed, 22 Feb 2023 20:32:56 GMT

Please post the complete SAS log step producing the errors.

Re: SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse

Sajid01 — Sat, 25 Feb 2023 14:37:17 GMT

As your back end looks like you are having MS SQL Server.
As a first step try running the code in SQL Server Management Studio

topic Re: SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse in Administration and Deployment

SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse

Re: SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse

Re: SAS 9.4 M7 ACCESS/ODBC INSERT statement failing Synapse