Cannot initialize the data source object of OLE DB provider "" for linked server "(null)".

[Mark Landry]

In trying to create a UDM based on a data source view using (local)
SQL Server (as primary) and two external data sources Teradata and IBM
DB2 UDB, I get the following errors when processing dimensions built
on the external sources:

OLE DB error: OLE DB or ODBC error: Cannot initialize the data source
object of OLE DB provider "IBMDADB2.1" for linked server "(null)".;
42000;

OLE DB error: OLE DB or ODBC error: Cannot initialize the data source
object of OLE DB provider "TDOLEDB.1" for linked server "(null)".;
42000.

What's causing the error? Do I need to create linked servers inside
SS2005?

I can access both external sources from SS2005 using OPENROWSET:

SELECT a.*
FROM OPENROWSET('IBMDADB2.1', 'database'; 'user'; 'password',
'SELECT * FROM schema.table') AS a;

[Mark Landry]

Answer: The OLE DB providers from IBM (DB2 v9.0) and NCR (Teradata TUF
12.0) expect the Data Source, User Id, and Password parameters to be
passed separately using the DBPROP_INIT_DATASOURCE,
DBPROP_AUTH_USERID, and DBPROP_AUTH_PASSWORD oledb properties
respectively. In SS2K5, this corresponds to the OPENROWSET syntax:

... OPENROWSET(<provider>, <mydatasource>; <myuserid>;
<mypassword>, <pass-through sql statement>) ...

Analysis Services 2K5 -- during dimension and cube processing only --
passes the connection info to SS2K5 (primary datasource) using the
connection string only (DBPROP_INIT_PROVIDERSTRING oledb property). In
SS2K5, this corresponds to the OPENROWSET syntax:

... OPENROWSET(<provider>, "Data Source=<mydatasource>;User
Id=<myuserid>;Password=<mypassword>, <pass-through sql statement>) ...

The IBM DB2 and Teradata OLE DB providers do not support this form.

The short list of OLE DB providers that can be used in AS2K5 multiple
datasource dataview can be found in OLE DB Providers Tested with SQL
Server http://msdn2.microsoft.com/en-us/library/ms187072.aspx
This list excludes the Teradata provider which is included
(incorrectly) in SS BOL "Working with Data Sources (Analysis
Services)" http://msdn2.microsoft.com/en-us/library/ms175608.aspx

As a work around, both IBM DB2 and Teradata OLE DB providers can be
defined as SS "linked servers" and SS views can be written to expose
the external tables and usable in an AS2K5 "data source view". Of
course, this is not as easy as using the pass-through queries that are
automatically built by AS2K5.