How to use Reporting Services to configure and to access an Oracle data source (834305)


The information in this article applies to:

  • Microsoft SQL Server 2000 Reporting Services

IN THIS TASK

INTRODUCTION

In Microsoft SQL Server 2000 Reporting Services, you can build and deploy a report that uses data from an Oracle data source. This article describes how to configure and access an Oracle data source for a report that you develop and deploy by using Reporting Services.

You can use an Oracle data source in a single report (that is, a report-specific data source), or you can use an Oracle data source as a shared source in several reports. The definition for a report-specific data source is stored in the report itself, whereas the definition for a shared data source is stored in a separate file on the report server. A report can access data from one data source or from many data sources that are report-specific or that are shared.

back to the top

Basic requirements to configure an Oracle data source

To access an Oracle data source in a report, you must first configure the Oracle data source. To configure an Oracle data source, you must complete both of the following steps:
  • Install the Oracle client tools on the report server computer that hosts the report, and then restart Microsoft Internet Information Services (IIS).
  • Install the driver that is used as the provider to connect to the Oracle data source. By default, the drivers are installed with Oracle client tools, with Microsoft Data Access Components (MDAC), or with Microsoft .NET Framework.
back to the top

Steps to configure an Oracle data source when you design a report by using Report Designer

When you develop a report by using Report Designer and that report requires an Oracle data source, you must either create a report-specific Oracle data source or create a shared Oracle data source to retrieve the report data. Additionally, you can configure a report-specific data source for a new report or a shared Oracle data source on an existing report. To configure a report-specific data source for a new report, follow these steps:
  1. Start Microsoft Visual Studio .NET 2003.
  2. On the File menu, point to New, and then click Project.
  3. Under Project Types, click Business Intelligence Projects.
  4. Under Templates, click Report Project.
  5. Click OK.
  6. In Solution Explorer, right-click the Report folder, and then click Add New Report.
  7. In the Report Wizard dialog box, click Oracle in the Type list.

    Note If you want to use this data source in multiple reports, make this data source a shared data source. To do so, click the Make this a shared data source option.
  8. Click Edit.
  9. Click the Connection tab, and then enter the Oracle data source details, such as the Oracle server instance name, user name, and password.
  10. Click OK.
  11. Click Next.
  12. In the Query String box, type the required query to obtain the report data, and then click Next.
  13. Click Finish.
To configure a shared Oracle data source for an existing report, follow these steps:
  1. Start Visual Studio .NET 2003.
  2. Open the existing project that has the report where you want to add an Oracle data source.
  3. In report design view, click the Data tab, and then click New Dataset in the Dataset list.
  4. In the Dataset dialog box, click New Data Source in the Data source list.
  5. In the Data Link Properties dialog box, click the Provider tab, and then under Select the data you want to connect to, click Microsoft OLE DB Provider for Oracle.
  6. Click Next.
  7. Click the Connection tab, and then enter the Oracle data source details, such as the Oracle server instance name, user name, and password.
  8. Click OK.
  9. In the Dataset dialog box, type the required query to obtain the report data in the Query String box, and then click OK.
Note If the report query string contains unnamed parameters, such as SELECT * FROM table WHERE column name = ?, you must edit the data source to set the data source type to OLE DB, and then click Microsoft OLE DB Provider for Oracle. If the report query contains named parameters, you can use the Oracle data processing extension instead of the OLE DB data processing extension.

The Graphical Query Designer uses OLE DB, and the Generic Query Designer uses the Reporting Services data processing data extension.

back to the top

Steps to configure an Oracle data source when you deploy a report by using Report Manager

When you deploy a report that uses a shared Oracle data source, you must configure the Oracle data source as a shared data source by using Report Manager. In Report Manager, you can configure an Oracle data source that uses either the OLE DB data processing extension or that uses the Oracle data processing extension. To configure an Oracle data source that uses the Oracle data processing extension, follow these steps:
  1. Start Report Manager.
  2. Click New Data Source.
  3. On the New Data Source page, type a name for the data source in the Name box.
  4. In the Connection Type list, click Oracle.
  5. In the Connection String text box, type the following text (where OracleServerInstanceName is the Oracle server instance name): data source=OracleServerInstanceName.

    Note: If you are using the Reporting Services data processing extension or OLEDB data processing extension, type the following text (where ProviderName is the name of data provider): Provider=ProviderName;data source=OracleServerInstanceName.
  6. Under Connect Using, click the Credentials stored securely in the report server option, and then type a valid username and password.

    Note Set the appropriate connection details, depending on your requirements.
  7. Click OK.

    Note The data source is created with the data source name that you provided.

The Oracle data processing extension uses the .NET Framework Data Provider for Oracle to connect to and to retrieve data from an Oracle data source. To download the .NET Framework Data Provider for Oracle that is available with Microsoft .Net Framework 1.1, visit the following Microsoft Web site:

Microsoft .NET Framework Version 1.1 Redistributable Package

Note You must install Oracle 8i Release 3 (8.1.7) Client or later on the report server computer for the provider to work as expected.

back to the top

Steps to troubleshoot error messages when you configure an Oracle data source

When you configure an Oracle data source that you developed and deployed by using Reporting Services, you may receive error messages. The following information can help you to troubleshoot these error messages:
  • If you receive the following error message, make sure that a valid username and password were used to log on to the data source:
    Test connection failed because of an error in initializing provider. ORA-01017: invalid username/password; logon denied
  • If you receive the following error message, make sure that you used a valid Oracle server instance name:
    Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve service name
    The Oracle server instance name that you used must be running. You may also receive this error message because of problems with the connectivity to the Oracle data source. For additional information about techniques to debug connectivity issues, click the following article number to view the article in the Microsoft Knowledge Base:

    259959 INFO: Techniques to debug connectivity issues to an Oracle server using the ODBC driver and OLE DB provider

  • If you receive the following error message, register or reinstall the provider that you selected to configure the Oracle data source:
    Test connection failed because of an error in initializing provider. Oracle error occurred, but error message could not be retrieved from Oracle.
    To work around this problem, select a different provider to configure the Oracle data source.
back to the top

REFERENCES

For more information, see the following topics in Reporting Services Books Online:
  • Retrieving Data from Other Data Sources
  • Connecting to a Data Source

back to the top
Modification Type:MinorLast Reviewed:2/28/2004
Keywords:kbReport kbProvider kbOracle kberrmsg kbHOWTOmaster KB834305 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.