cancel
Showing results for 
Search instead for 
Did you mean: 

ODBC vs OleDB when connecting to MS SQL Server

ashkhan
Meteor

I am trying to connect to Microsoft SQL Server and i am trying to figure out the right process. The only way i can connect to an ODBC connection is if i run Alteryx as an Admin go to Options>Manage Data Connection>Add Connection and then add the connection. Once added as an Admin i can close Alteryx and then when i restart it normally it will always appear as a dropdown connection which works fine although not intuitive because it seems like anytime i want to add an ODBC connection i need to start Alteryx as admin add the ODBC etc. It is also problematic because when i share the wf with a coworker he will get errors until he has the same ODBC connections added on his machine (we dont have Alteryx server). 

 

The only way i found around this was to use OleDB data connection for MS SQL because that connection string is stored and you dont have to login as an admin every time you need to change the connection to another DB on the server and/or when you send the wf to a coworker. 

 

So am i doing anything wrong or is that how ODBC is intended to be setup ? 

 

Screenshot shows if you dont login as admin all options to start a new connection are greyed out.

 

ODBC.jpg

  • Best Practices
  • Common Use Cases
  • Database Connection
Highlighted
Alteryx
Alteryx

Hi @ashkhan,

 

The screenshot that you have included is the Direct SQL connection, not the ODBC Connection. In the screenshot you have, you can type a name into the top field and then add the connection string. If you need to create a system connection, then you will need to start Alteryx with Admin privileges, however you should be able to create User Connections without Admin Privileges.

 

Using the new Direct Connections, it is just an Alias stored in the workflow. There is a few reasons for this, but as you have mentioned, you don't have Server and so some of those reasons will not apply to you. Using the OleDB connection as you would have pre-v11 will be the easiest way for you to tick off your use cases.

 

Kane

RussellD
Alteryx Alumni (Retired)

Hi @ashkhan

To add to the great info @KaneG gave. Here are some links to 11.0 updates for the Input tool and the new Manage Data Connections that might be helpful.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Input-Data-Tool-Enhancements-Alteryx-11-0/ta...

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Manage-Data-Connections-Alteryx-11-0/ta-p/45...

I wish I could give this more stars!  The new Data Input is NOT INTUITIVE at all.

Very confusing when your IT department requires OleDB for SQL Server.