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

alteryx server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

Error when saving workflow with In-DB connection to Alteryx Gallery

Alteryx Partner

When I try saving a workflow to my Gallery I receive two errors: (1) resulting from a dynamic input tool and (2) resulting from an In-DB tool. These issues do not occur when I run them on the local machine, only when I try saving it and allow Alteryx to 'validate workflow then save'.

Error_on_Save_to_Gallery.png

Additional detail about these issues: 

Issue (1): This workflow downloads zip files containing CSV files from a site, extracts the CSVs from the zips, extracts the records from the CSVs, transforms the data, and ultimately pushes the data to my database. I'm using the Dynamic Input tool to handle the data extraction from the individual CSVs. This issue did not occur before I tried implementing new tools mentioned below in issue (2). The CSV template needed for the Dynamic Input tool is saved on my local machine and the flow runs without issue locally, however, when I try to save to the gallery I receive a "File Not Found" error.

Issue (2): I was previously using a normal input tool with a summarize tool to get the last RecordID from the table I'm pushing data to in order to generate the correct RecordID with each run. This configuration caused the flow to lag as it DB table grew, so I chose to implement a new solution with the In-DB tools. Initially, I had connectivity issues with the 'Connect In-DB' tool but was able to resolve those by creating a system connection (as opposed to a user connection) and selecting the correct driver. Since then, I have had no trouble running the flow locally, only when I attempt to save to the gallery and then I receive an "Error opening '[query]': No columns returned" error.

In-DB_Connection.png

 

Can you all help me determine what might be causing these issues and how to resolve them?

 

Issue #1:
When you save to the Gallery, go to the workflow options then click on the Manage Workflow Assets. Are you including the template file with the workflow?

Issue #2:

Is the In-DB connection set up on the Gallery exactly the same as it is set up on your machine?  

Alteryx Partner

Thanks for the response @patrick_mcauliffe - I discovered yesterday, via a copy of this post to the Designer discussion board, that I need to package the template file with the workflow. Therefore, issue #1 has been resolved.

 

As for issue #2 - I've actually never heard of what you're suggesting, so I don't know if the In-DB connection is set up the same on the gallery. In fact, I had no idea that I could set up an In-DB connection in the gallery. I thought all the connections were handled in Designer.

 

I did some digging on the Alteryx Community and found this post that walks through how to create an In-DB connection file that can be packaged to the workflow as an asset. I set up everything successfully but still got the same error. I'm not sure if this is what you had in mind, but I still can't seem to figure this out. Perhaps you can clarify what you mean when you say, "set up on the Gallery exactly the same as it is set up on your machine"?

Alteryx Partner

@patrick_mcauliffe  - Posting a screenshot of a successful run on the flow locally along with the query that I'm running in the Connect In-DB tool.

Local_Successful_Run_of_ERCOTRTLMP.png

Select * From database.schema.table_name

 

Alteryx Partner

After a call with @KevinP I was able to resolve the second issue by creating an In-DB Connection with a SQL Server Ole DB driver, replacing the SQL Server ODBC driver. In order to get this driver to work I had to make these few configurations:

  1. Integrated Security: Open this prompt and then close it (this is effectively an "acknowledgment" of this setting in Alteryx)
  2. Persist Security: Open this prompt and set it to "True"

 

Kevin also offered a solution that replaced the need for the In-DB tools altogether. He suggested using the regular input data tool and writing the following optimized query which returns the last RecordID:

 

Select Top 1 RecordID From Database Order By RecordID Desc

This solution, in particular, was a great reminder to leverage SQL and experiment with queries if the tools begin to pile up. Thanks for your assistance, Kevin!

Highlighted
Alteryx
Alteryx

@dallen Happy to be of assistance and I am glad to hear the solutions provided are working well for you. Also thank you for updating the post with the solutions as it may help other users in the future.