I have a proc that when executed moves data from STAGING database to CORE database. It is not too complicated. NOCOUNT = ON.
The proc executes perfectly when run from anything but Alteryx. When executed from Alteryx is appears to execute normally but does not complete the process as desired. I believe that it is performing a rollback that generates an error after if completes.
Results in no data loaded and no internal log file entries:
Executing PostSQL: "EXEC [AUDIT_STAGING].[NHEATMAP].[SP_LD_NHEATMAP_ALTERYX_DATA] '110109';" : [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (Tool Id: 10)
Error running PostSQL on "AUDIT_STAGING.ALTERYX.STG_110109": [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (Tool Id: 10)
Works no problem with all success.
EXEC [AUDIT_STAGING].[NHEATMAP].[SP_LD_NHEATMAP_ALTERYX_DATA] '110109';"
from any SQL compliant tool like MSMC or Toad.
I understand that NOCOUNT=ON is required and all messaging back to the Alteryx client is shut off. I am thinking that the process attempts a rollback that fails. Let me know if y'all have encountered this. Cheers
