Upgraded to Newer Version but Lost My SQL Server Connection and Negative Impact SQL

[Chad_Cox]

I just updated to a newer version of Designer and had to create a NEW connect to my SQL Server.  However, each time I update an old workflow to the NEW connection it wipes out the SQL statement in the workflow's input tool.

In the attached example you see the previously saved workflow now has an invalid connection.  When I update this to the new connection the SQL statement in option 7 Query will be wiped out.

Is there a way to prevent losing all my SQL Statements when I update the connection?

[KGT]

The best way to do this, would be update the connection in DCM. So, that way all the workflows that refer to that connection, now look at the new connection, as they are linked to the DCM Connection.

If updating the Input Tools directly through the GUI, I can't see how you would preserve the SQL Statements. As soon as you change the connection, it treats it like a fresh Input tool.

Other options include changing the XML manually, but are just workarounds with obvious things to look out for. You could always scrape the yxmd files for all the queries and then manually put them back in one by one, but again that's a workaround.

[Chad_Cox]

Where would I update the connection in the DCM?  I'm not finding a DCM option in my menu. 

[KGT · Accepted solution]

Take a look at this: https://community.alteryx.com/t5/Engine-Works/What-is-Alteryx-Data-Connection-Manager/ba-p/983826

I don;t want to give advice that may not be totally accurate.... I'm running 2022.3 and no server connection, so my interfaces may be different. On the input tool, if you tick DCM before loading "Set up a connection" then you will get slightly different options.

[Chad_Cox]

Thanks for the assist.  I was able to go to Manage Connection and then use the Backup and Restore options to save my connection settings on my current machine, then use those saved settings and restore them to my new machine and this allowed all my saved SQL statements to the server to remain intake and working.

[JPetrie]

I was able to solve this problem in a different way. Prior to setting up a new connection, within the connection configuration window, the SQL query row is inaccessible when you click the "..." and try to get back into the Query and copy it; it just says "Missing Object". HOWEVER, you CAN right-click the row and select "copy" and paste this into notepad, delete the prefixed "QUERY", and  delete the suffixed "..." - You should then be good to go update you connection and paste back in the query.

[ajinaniyan123]

When you upgrade to a newer version of Designer and create a new SQL Server connection the tool can reset queries in the input step To avoid losing your SQL statements copy them out to a text file before updating the connection and paste them back afterward A better approach is to use a DSN with a consistent name so old workflows automatically point to the new connection without wiping the queries Another method is to directly edit the workflow file replacing only the connection string while keeping the SQL intact If you encounter any corruption or missing database objects during this process Stellar Repair for MS SQL can help restore your database to a consistent state