OLE DB Connection Type (SSRS)

To include data from an OLE DB data provider, you must have a dataset that is based on a report data source of type OLE DB. This built-in data source type is based on the Microsoft SQL Server Reporting Services OLE DB data processing extension.

OLE DB is a data access technology that enables clients to connect to a variety of data providers. After you select the data source type OLE DB, you must select a specific data provider. Support for features such as parameters and credentials are dependent on the data provider that you select.

Use the information in this topic to build a data source. For step-by-step instructions, see Add and Verify a Data Connection or Data Source (Report Builder and SSRS).

In This Article

Connection String

Credentials

Parameters

Remarks

How-To Topics

Related Sections

Connection String

The connection string for the OLE DB data processing extension depends on the data provider that you want. A typical connection string contains name/value pairs that are supported by the data provider. For example, the following connection string specifies the OLE DB provider for SQL Server Native Client and the AdventureWorks database:

Provider=SQLNCLI10.1;Data Source=server; Initial Catalog=AdventureWorks

The connection string that you use depends on the external data source that you are connecting to. To set connection string properties specific to a data provider, from the General page of the Data Source Properties dialog box, click the Build button to open the Connection Properties dialog box. Set extended data source properties through the Data Link Properties dialog box.

For examples of connection strings, see Data Connections, Data Sources, and Connection Strings in Report Builder.

Back to Top

Credentials

Credentials are required to run queries, to preview the report locally, and to preview the report from the report server.

After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.

For more information, see Data Connections, Data Sources, and Connection Strings (SSRS) or Specify Credentials in Report Builder.

Special Characters in a Password

If you configure the OLE DB data source to prompt for a password or to include the password in the connection string, and a user enters the password with special characters such as punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" may indicate this problem.

Note

It is recommended that you do not add login information such as passwords to the connection string. Report Builder provides a separate tab on the Data Source dialog box that you can use to enter credentials.

Back to Top

Parameters

Some OLE DB providers support unnamed parameters and not named parameters. Parameters are passed by position by using a placeholder in the query. The placeholder character is determined by the syntax that is supported by the data provider.

Back to Top

Remarks

OLEDB is a native technology for building data providers for specific data sources. OLEDB is based on COM (Component Object Model) interfaces. OLEDB is a later technology than ODBC and earlier than ADO.NET data providers. OLEDB data providers are registered with the operating system like any other COM component. OLEDB data providers are available from Microsoft and third party vendors. Microsoft also provides MSDASQL, an OLEDB data provider that bridges communication to ODBC drivers. For more information, see ODBC Connection Type (SSRS).

To successfully retrieve the data that you want, you must provide query syntax that is supported by the data provider. Parameter support varies by data provider. For more information, see topics that are specific to the data provider that you select. For example:

• Analysis Services OLE DB Provider (Analysis Services - Multidimensional Data)

• Using the .NET Framework Data Provider for Oracle

• SQL Server Native Client (OLE DB)

For more information about specific OLE DB data providers, see Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.

Back to Top

How-To Topics

This section contains step-by-step instructions for working with data connections, data sources, and datasets.

Add and Verify a Data Connection or Data Source (Report Builder and SSRS)

Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS)

Add a Filter to a Dataset (Report Builder and SSRS)

Back to Top

Related Sections

These sections of the documentation provide in-depth conceptual information about report data, as well as procedural information about how to define, customize, and use parts of a report that are related to data.

• Add Data to a Report (Report Builder and SSRS)
  Provides an overview of accessing data for your report.

• Data Connections, Data Sources, and Connection Strings in Report Builder
  Provides information about data connections and data sources.

• Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)
  Provides information about embedded and shared datasets.

• Dataset Fields Collection (Report Builder and SSRS)
  Provides information about the dataset field collection generated by the query.

• Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.
  Provides in-depth information about platform and version support for each data extension.

Back to Top

See Also

Concepts

Report Parameters (Report Builder and SSRS)

Filter, Group, and Sort Data (Report Builder and SSRS)

Expressions (Report Builder and SSRS)