Dear all,
I'm trying to build a rather simple workflow that compares the structure (schema) of a number of tables between the source database and the distention database. The idea is simple if detect changes run a stored procedure, call API, etc.
My idea is to a create a table (alteryx_lookup) contains:
and I will loop thought this table system by system.
I have tested it when comparing between different databases tables within the same server it runs as excepted but now I want to make it run where the source and target are not in the same server, I have faced couple of problems and challenges.
Challenges so far:
Questions:
Please let me know if you need any clarification.
Your help is highly appreciated.
Thanks.
This is a tough one since you are dealing with connections. You may find that it may be easier to just setup all the connections and then do comparisons rather then attempting to update connections on the fly. If you did go that route, you could then create a batch macro to compare the schemas based on a comparisons lookup table. This would at least take the connection issues you may run into out of the equation.
If you did decide to go the dynamic route you would have to provide the entire connections string, password and all to update the tool. The issue you may run into however, is the actual call to the database when switching or you could run into driver issues as well since they may use different drivers.
I would suggest next time to provide a workflow of what you have attempted thus for so that the Community members can take a closer look at what you have attempted and your logic.
Hopefully this helps.