DSN-less connection string issue

uahmed90

Hi All,

     I've been having this issue with Alteryx Designer. So using normal ODBC connection in Designer to append data to a table in MS SQL Server works just fine, but when uploading to the Alteryx server, I am required to change the settings to a DSN-less connection, meaning I need to use the ODBC connection string.

I am using: odbc:DRIVER={SQL Server Native Client 11.0};Uid=;Pwd=;Database=;Server=; format

Doing this doesn't work, as you can see there is no option for "Table Name". So when I do this, I get the "Dynamic Input: No table chosen" error, which I can understand. Looking through forums, I see that I'm supposed to concatenate the table name at the end of the connection string, but whenever I do that, I get an error " Cannot login for the user: ". It starts to give me a login error, when it doesnt if I don't concatenate the table name. I tried: 

Concat table name to database

odbc:DRIVER={SQL Server Native Client 11.0};Server=;Uid=;Pwd=__EncPwd1__;Database=BP_test|||dbo.shipment_tracking

Concat table name to password (some forums online did it this way)

odbc:DRIVER={SQL Server Native Client 11.0};Server=;Uid=;Database=BP_test;Pwd=__EncPwd1__|||dbo.shipment_tracking

None of these options seem to work. I tried the changing the fullpath to include the table name, that didnt work either. Any idea how I can fix this? 

patrick_digan

---

@uahmed90 wrote:

So using normal ODBC connection in Designer to append data to a table in MS SQL Server works just fine

---

@uahmed90 when you look at the messages in the results window for normal ODBC connection that is working, does it spell out the connection at all? And I apologize for such a dumb suggestion, but did you type your actual password into the output tool box and let alteryx hide it/convert it to __EncPwd1__? I only ask because when I copied the connection string from the results window to the output tool, I got a similar login error to you because it thought my password was __EncPwd1__. I had to type in my password in the output tool and then it worked fine.

I'll just walk you through my experience in case it helps. It's not SQL Server, but my output data is configured like this using an alias:

My results window shows what this was translated to:

So I can just copy that to my output tool (and type in my password over the __EncPwd1__ part) to get this which works:

So to make it DSNless, I went to my 64 bit ODBC administrator and got a few pieces of key info to create a DSNless connection like this which works (note that I had also googled my driver to get more info about its syntax):

So for my specific connection, I just had to add my Driver and System. When I've connected to SQL server in the past, I think you can use one of these 2 options depending on how your SQL server is setup (adapted from https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/). 

With user name and password:

odbc:Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword|||TableName

Without user name and password (ie passing your windows login):

odbc:Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes|||TableName

Hope that helps!