Problems with OLEDB connection to SQL server

[#1]

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

[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.

[#3]

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.

[#5]

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]

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.