Alteryx Gallery Running SQL before evaluating the inputs from the interface in the Macro

Question

Hi All,

I am having an issue with a Macro that we want to give our users to allow them to interact with one of our databases.

The macro takes a user input, and updates the value of a schema / table name in the Macro at the start of its run. However it keeps evaluating the SQL statement with the template text before replacing it with the user input, thus giving a permissions error saying they don't have access to the database.

Why is this happening and can anyone help me with a work around?

Thanks

A

alecmanders93 · Answer

Hi Joe,

Thanks for getting back to me. It turned out i was looking for the error in the wrong place, it was being cause by the default database set in the  Hadoop ODBC connection, which just happened to be the same name of database that i was using as the "template" code that would get replaced by the users input.

Thanks anyway.

JoeS · Answer

Hi @alecmanders93

Could the error be occurring due to the connection string not working instead?

Are you able to test a simple workflow using the same connection on the server in an input tool and checking that works?

My guess here is that the connection string is using windows auth and the RunAs on the server isn't set with the right permissions.

I could be off, but can't think why else you'd be getting a connection error.