Easiest way to test access to all tables via input tool

Question

I am currently attempting to connect to a Sage Intact database via a third party driver C Data.

My goal is to test that I am able to actually see the data for each table that exists in the Sage Intact source. However, some tables, for whatever reason, show a permission error or say invalid object type. There are roughly couple hundred tables and for me to create couple hundred input's is just not a good idea.

Anyone have any good ideas as for the easiest and best way for me to do this? Preferably if you can show me? I was thinking a macro could do it, but honestly I wouldn't know how to do that.

Multi errors.png

TrevorS · Answer

Hello @Iggypop

You should be able to create a batch macro to do this.

Build the SQL query with the SELECT * FROM TABLE, creating 1 query for each table, then send that into the batch macro.Make sure you output the Table name in the batch macro, and then you can join back to the original table names to see what falls out (aka "it didn't read these tables".)I hope this helps!

Iggypop · Answer

I’m not looking for a solution on how to connect or not connect to the sage intact database. I’m looking for a way for me to be able to check which tables specifically connect and don’t connect rather than having to create 100+ input tools and checking.

let’s pretend it isn’t safe intact and it is a regular input tool attempting to access a table in SQL what’s the best way for me to have to go through all the tables and check if it connects or not.

TrevorS · Answer

Hello @Iggypop

Thanks for reaching out to the Community!
Currently, the Sage Inttact DB is not a supported Data Source with Alteryx. This is likely what is causing issues with the data accessing error messages.
I assume that you are using this CData tool for accessing the Sage Inttact data?
If so, you will need to reach out to them for assistance as we do not support 3rd party apps and macros.

Thank you,
TrevorS