topic Re: Problems with OLEDB connection to SQL server in QlikView

Problems with OLEDB connection to SQL server

Tue, 08 Jul 2014 15:39:54 GMT

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

Re: Problems with OLEDB connection to SQL server

Gysbert_Wassenaar — Tue, 08 Jul 2014 15:43:33 GMT

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.

Re: Problems with OLEDB connection to SQL server

Tue, 08 Jul 2014 15:52:24 GMT

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?

Re: Problems with OLEDB connection to SQL server

Gysbert_Wassenaar — Tue, 08 Jul 2014 16:05:32 GMT

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.

Re: Problems with OLEDB connection to SQL server

Wed, 09 Jul 2014 11:55:56 GMT

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!

Problems with OLEDB connection to SQL server

jakob_rasmussen — Thu, 28 Feb 2019 13:27:52 GMT

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.