Input Tool Connect To Saved Data Connection - Asking to resolve file type?

[StephenMack]

So I can pull in data from my database from an input tool, but now I'm trying to put the data connection on the gallery so that it can be shared among my team.

When I select the data connection it immediately gives me a popup asking to resolve the file type:

I don't get this when I manually connect to the database from an input tool. And any option I try gives an error, either root element missing, or something like "the file 'aka:5absdsdlfkj' does not exist."

Is there something I'm missing?

[Inactive User]

The Gallery Connection that was made may not be an appropriate connection string. Can you copy that connection string into an input tool and does it work? If not, then it is probably that issue.

[StephenMack]

I have verified that connection string with other software and it connects and runs queries just fine.

Even if it wasn't correct I'm not sure what that would have to do with the resolve file type. I'm kind of confused as to what alteryx is doing here

[JessicaS]

Hi @StephenMack,

That error does generally indicate an invalid connection string.  See here for a bit more info on that window popping up with a saved data connection.   Sometimes with alteryx you have to add odbc: (odbc) or odb: (oledb) to the beginning of the connection string so that could potentially be the missing syntax. I would recommend trying the same string in an input tool or in manage data connections within designer desktop to see if you get any errors as @Inactive User mentioned.

If you are still stuck, let us know.

[StephenMack]

When adding 'odb:' to the beginning of my connection string I get: "Connection Error: An ALE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB'".

When adding 'odb:Provider=SQLOLEDB;" to the beginning of my connection string designer freezes for a while and finally gives me "Connection Error: [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access is denied."

When adding 'odbc:' to the beginning of my connection string I get: "Connection Error: ERROR [IM002][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

I'm sorry I'm kind of confused by all this, I've never needed more than the connection string before. From other software on this computer I can query just fine with only the connection string.

[StephenMack]

I just tried adding an input tool and simply copying the connection string into the box (never realized you could do that) and I still get the resolve filetype box.

When I go to Other Databased > ODBC > ODBC Admin I can get it running. But that only gives me a connection string of "odbc: DSN=Name_I_Gave;UID=username;PWD=password"

There's no data in the connection string about the actual database name or location. I'm assuming that's wrapped up in the DSN, but is there a way to put that on the server?

[StephenMack]

Ok I think there's been a breakthrough. So on the server I was able to find the "ODBC Data Sources" and create a 'System DSN' for the database by setting it up through the MySQL driver. Then I guess I have a DSN set, so the connection string 

"odbc: DSN=Name_I_Gave;UID=username;PWD=password" seems to work.

When I did the odbc connection before manually it must not have given the entire computer a DSN, but just that instance?

Seems to be working, now to share it with the team and hope! Fingers crossed!

[StephenMack · Accepted solution]

Turns out in that case you need to create a DSN on each machine, meaning sharing it on the server isn't really helpful at all.

To do a connection string without a DSN I had to dive into the driver DLL files. Apparently the MySQL driver isn't just "odbc:DRIVER={MySQL ODBC 5.3 Driver}" but "odbc:DRIVER={MySQL ODBC 5.3 Unicode Driver}".

Now it looks like it's working...and I never have to go through this mess again!

[apdata]

Hi Stephen - Can you please clarify further on creating System DSN?

We have several SQL Server DB's - in ODBC Config where the Alteryx Server is installed - do we need to create a System DSN for each DB individually?