How to create OLEDB Connection in gallery?

anil_m

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.

patrick_mcauliffe

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.