ODBC Connection SQL Server

[PhilippZiemer]

Hey,

I want to connect a SQL Server Database through ODBC. I do not want to add a ODBC Data Source but want to add a connection string in QlikView. Which is the correct driver?

ODBC CONNECT TO 'Driver(?); DbName=c:\Database.mdb' (XUserId is enxrypt_user, XPassword is encrypt_password);

An analogue working statement for an Interbase Firebird Database would be:

ODBC CONNECT TO 'FIREBIRD; DbName=c:\Inter_Database.IB' (XUserId is user123, XPassword is password123);

The connection in ODBC Data Soruces:

Thanks

Philipp

[Colin-Albert · Accepted solution]

Use the ADO (OLE DB) connection string rather than ODBC.

Select OLE DB, then click Connect and choose "Microsoft OLE DB Provider for Microsoft SQL Server" from the list.

ADO is faster and does not rely on a connection string being setup in control panel.

[#3]

Hi Philipp,

   You can use the OLEDB method under Database section.

[stevedark]

Hi Philipp,

Colin is correct if you are looking to connect to a SQL Server.  It would appear from your connection string that you may be trying to connect to an Access database (.mdb)?

In which case you should tick the Force 32 Bit tick box and use either the Microsoft Jet or the Microsoft Office Access driver.  See which one works best for you.

ODBC is generally a last resort when you can not get any other connection method to work (for old flat file databases, for example).

Hope that helps,

Steve

[Colin-Albert]

Well spotted Steve, I missed the mdb reference in the strings and was seeing the "SQL Server" in the image.

[PhilippZiemer]

Still was correct. An OLE DB connection to the server works just fine - thanks

[flipside]

Hi Steve,

Of course Microsoft have done a turnaround regarding OLE DB and support will (eventually) be removed with regards to SQL Server, and ODBC may once again rule !

When I can post links without being moderated, I'll add them.

flipside

h ttp://en.wikipedia.org/wiki/OLE_DB

h ttp://blogs.msdn.com/b/adonet/archive/2011/09/13/microsoft-sql-server-oledb-provider-deprecation-announcement.aspx