Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problems with OLEDB connection to SQL server

Hi, I am trying to connect to a SQL server from my computer.

I have done this before (but not from this computer) and I know the basics.

I am using the "Microsoft OLE DB Provider for SQL Server" and I have all necessary info such as ServerName, UserName and password. The connection works and the OLE DB string is created but once I try to access the database no tables or views are visible or accessible.

I have also tried using ODBC with the same result.

When I try to do the same thing using Xls it works and I can see all of the tables and views in the data base.

Does anyone know what to do?

Thanks!

//Hans

5 Replies
Gysbert_Wassenaar

Make sure you select the right schema. It's possible the user you use to connect with doesn't own any tables, but does have priviliges to read tables in another schema.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert, I have tried different schemas. The schema "master" works, then I can see the tables. But I am not interested in that schema.

If I am missing out on some privileges how come it works when i use Excel?

Gysbert_Wassenaar

Actually, master is a database (at least afaik). Perhaps you've got the wrong database too. A SQL Server instance can have many databases. Perhaps you should ask your local friendly database administrator to help you get connected correctly.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi Gysbert, thanks for your answers!

how do I select a specific schema?

When creating the OLE DB Connection string I can select "Servername" and "database" but NOT "Schema".

When I create an OLE DB Connection in Excel using the exact same information it works.

My guess was that I lacked some privileges on the SQL-server and that my user didn´t have access to the specific views. But since it works in Excel I am very confused!

jakob_rasmussen
Contributor III
Contributor III

I just had a similar issue in Qlik Sense with the OLE DB connector, and ended up finding help in an old QlikView knowledge base artice (000048672). If the database name contains a period character the connector can end up being confused.

Example
Server: sqlserver01
DB name: My.database.name

That confuses the GUI when selecting data from schemas, but if you just insert the connection string and throw an SQL statement it will work just fine.