Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

DSN-less connection string issue

uahmed90
7 - Meteor

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? 

 

alteryx issue.png

6 REPLIES 6
randreag
11 - Bolide

hi @uahmed90 

 

Normally you can create the connection in the server and then you can choose this connection from your designer (you have to be connected to gallery) so when you upload it to server you don't have any trouble.

 

In this way is just calling the connection name(from the gallery)  and adding the name of the table in the output tool

 

I hope it helps

uahmed90
7 - Meteor

Problem with that is that I do not have access to do that. Any other workaround idea?

randreag
11 - Bolide

well, actually is important that the server can access the database so if doesn't exist the connection there you are not going be able to connect in the server

 

An administrator has to create the connection.

Another way yoi can try, will be using a file that the server can have access, connecting using the indatabase tools

 

But the problem with that is the option update if exist doesn't exist, you will have to create a way to validate that

 

 

patrick_digan
17 - Castor
17 - Castor

@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:

patrick_digan_0-1612363251499.png

My results window shows what this was translated to:

patrick_digan_1-1612363335488.png

 

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

patrick_digan_4-1612363602727.png

 

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):

patrick_digan_6-1612363803654.png

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!

 

 

uahmed90
7 - Meteor

WOW, you are a GENIUS. It was using __Encdpwd__ as the password, even though I did initially write down my password there. But I guess somewhere along the way it switched back? Not sure, but yeah re-typing the password there got it to work. I also used added "trusted_connection=yes" at the end of the string and then concatenated the table name to that, and that worked, both in designer and on published in the gallery! Thanks so much! 

patrick_digan
17 - Castor
17 - Castor

Glad I could help!

Labels