Problem: One of the users was unable to refresh an existing DESKI report and was getting the following error
Connection or SQL sentence error: (DA0005)
Followed by connection unavailable error (DA004)
- Cause 1: You may get a connection or SQL sentence error after changing the date format in the sbo file.
Action: Verify that the DATE_FORMAT parameter is set to the date format that you have set in the sbo file.
- Cause 2: Your universe connection has been moved, deleted, renamed, or account accessing data changed access rights.
Action: Check connection and its parameters. Data Access driver, connection and login parameter and connection type
- Cause 3: Your drivers have been moved, deleted, renamed, or damaged.
Action: Install the Microsoft drivers again.
After checking all of the above I found out that universe was using OLEDB connection and also user has moved his machine.
I installed SQL Native Client for the appropriate version of the SQL Server universe tried to access on his machine and it worked.
That led me to the questions:
What is the difference between OLE Db and ODBC data access?
What is preferred method connecting to databases today?
Comparing OLE DB and ODBC
OLE DB and ODBC are both specifications created by Microsoft to address universal data access.
ODBC is an industry standard and a component of Microsoft® Windows® Open Services Architecture (WOSA). The ODBC interface makes it possible for applications to access data from a variety of database management systems (DBMSs) through a single interface. The application in this case is independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which create an interface between an application and a specific DBMS.
Microsoft’s definition of OLE DB is a strategic system-level programming interface to data across the organization. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data.
|Component based specification||Procedural based specification|
|Supports all forms of data stores: relational,
hierarchical, e-mail and file system stores; text, graphical, and geographical data; custom business objects; and more
|Constrained to relational data stores|
|Richer and more flexible interface for data access||Tightly bound to a command syntax|
|Better support Internet integration and multi-threading||More established interface proven drivers
and applications available in the market place
|Easy to deploy||Difficult to deploy due since many software pieces must be synchronized (server, driver, OS, etc)|
|Accomodates several locking models that allow multiple concurrent updates||No concept of record locking|
|Worked only in Windows work. Mostly for Microsoft data systems||Access data from multiple platform|
We have been told many times by Microsoft to start migrating applications to OLE DB because it is the industry direction to access all kinds of data and it’s fully integrated in Microsoft’s desktop environment.
However recently, Microsoft made an announcement that sounded like a cruel joke. After years of telling users that Open Database Connectivity (ODBC) was an old technology that should no longer be used and would be phased out in favour of Object Linking and Embedding Database (OLE DB), they declared ODBC the new king of data access and deprecated OLE DB.
Why ODBC is a winner for the data access?
The explanation is obvious — ODBC has survived and prevailed mainly because there is a strong need to access database systems from multiple platforms. OLE DB worked only in the Windows world and an OLE DB provider was usually a Microsoft product.
In the last few years, the need for cross-platform and cross-database communication greatly increased. There are lots of new database products, many of them based on NoSQL technologies. Heterogeneous database systems on multiple platforms including Windows, Linux, iOS, Android etc need to communicate with each other to satisfy the needs of business users. Structured data systems based on relational databases often need to connect to unstructured data stores.
Microsoft realized, based on surveys and customer feedback, that ODBC is the most established cross-platform data access technology.
Will this affect your existing applications?
OLE DB for SQL Server will continue to be supported for several years. Mainstream support for SQL Server 2012 will last until July 2017.
Many components of SQL Server use OLE DB, including linked servers, distributed queries, SQLCMD utility or OLE DB for OLAP. So Microsoft will now need to re-architect these components and figure out how to make them work either with ODBC or some other way of communicating at the SQL Server level.