Hi,
I have created a OleDb connection in gallery data connections, I tried the following two formats.
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;
and
Provider=SQLOLEDB;Data Source=Your_Server_Name; Initial Catalog= Your_Database_Name; UserId=Your_Username; Password=Your_Password;
The above two formats are not working and throwing below error message
When I use odbc connection string, i am getting the following error message
Error: Dynamic Input (6): Error SQLExecute: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cursors are not supported on a table which has a clustered columnstore index.[Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
Since my table has clustered columnstore index, I have to use oledb connection, But not sure how to connect.
Solved! Go to Solution.
When you're setting up an OleDb connection to SQL Server, you can't use the "SQL Server" connection setting. Instead, use the "Other Databases" heading and selection from the sub-menu "OleDb".
From there you can set it up just as you normally would.
After you've done this in Designer in an Input tool, you'll have the connection string if you wanted to paste that into setting up a Gallery data connection.
When configuring the In-DB connections I do not see an "Other Databases" option. I was able to successfully use Microsoft SQL Server for the In-DB configuration and use an OLE connection. This runs fine in the Alteryx desktop but it cannot connect in the Gallery. I am not an admin for our Alteryx server and need to know what I should ask for on the Gallery settings in order to get my workflow to run in the Gallery.
Thanks
Hi @C291685 - In-DB connections are configured differently than standard ones on Gallery.
You can reference this article: https://community.alteryx.com/t5/Alteryx-Server-Knowledge-Base/How-to-Run-In-db-Workflows-on-Server-...
For the above method, the server admin would need to modify the ODBC settings on the server machine(s).