community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Truncate Table Error Writing to SQL Database after 11.3 Upgrade

Meteoroid

I had been avoiding upgrading to 11.3 since things like this always seem to pop up so this is a little late but maybe someone will still find it helpful.

 

I had a number of workflows writing to SQL tables via ODBC connections.  After the upgrade, some still worked while others threw the following error -

 

Error running TRUNCATE table "TableName": [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot find the object "TableName" because it does not exist or you do not have permissions.

 

All of the connections were using the Delete Data and Append output option.

 

I knew upgrading could not have changed my permissions on the tables or have made the tables disappear but just to be sure I checked that I could read the tables in and that I still had the crucial and sometimes missing "Delete" permission on all the tables.  All of that was unchanged.

 

The clue here seems to be this Truncate table error.  I'm no SQL wiz but I researched that function here: https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql

 

In the permissions section it says the following:

The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

 

I checked with my DBA and although I did have DELETE permission on the table, I did not have ALTER permission.  Once I got it, I could write to the table no problem.

 

The backend SQL on the output tool for Delete Data and Append output option must have changed between 10.5 and 11.3 and my companies policy of granting the minimum permission seems to have stopped my ability to write to these tables.  The tables that I could write to were in a data lake that I had wide open access to.

 

Hope this helps someone else having issues.

 

 

Meteor

Hi @andrew_moore,

 

I recently upgraded to 11.7 and found this issue as well and I was able to "Delete Data & Append" before the upgrade. Unfortunately, my DBA cannot grant ALTER permission due to our internal security policy. Here is our resolution and it works fine.

 

Use “Pre Create SQL Statement” to Delete the data and use “Append Existing” instead. It works fine. Just want to share another option. Alteryx Append Data Screenshot.png

 

 

Labels