Transports and Interfaces: Siebel eBusiness Application Integration Volume III > Using Siebel OLE DB Provider > Connecting Siebel Data Using OLE DB Consumers >

Viewing Siebel OLE DB Rowsets in Microsoft Office Applications

This section discusses a variety of ways to view information using Microsoft Office applications.

Business analysts find the Siebel OLE DB Provider support useful for analyzing account data and other information stored in the Siebel Database and incorporating that data in an Excel spreadsheet. To use Siebel OLE DB Provider from Excel, you create an external query that connects to Siebel OLE DB Provider.

To view Siebel data in Microsoft Excel

1. Open Microsoft Excel.
2. Choose File > New to open a new spreadsheet.
3. Create a query using Notepad or any text editor. If you want to use a different data source than the one defined in the .cfg file you can do so here.
4. Enter the connect string, and any other information.
5. Save your work.

The following example connects to Siebel OLE DB Provider and sends a command to retrieve all records from the Contact virtual table where the position is equal to Manager. You can store any query in a *.rqy file and execute the query at a later time.

The properties identify the contents of the file as an OLE DB type query and provide the connection parameters and query text. The properties QueryType, Version, Connection, CommandType and CommandText are required. The structure defined is mandatory and cannot be changed.

Table 21 shows the required properties for an Excel query file.

For example:

QueryType=OLEDB
Version=1

Connection=Provider=SiebelOLEDB.Provider.1;Password=db2;
User ID=SADMIN;Data Source=siebel://10.24.20.5/siebel/sseobjmgr;

CommandType=Default

CommandText=select "City" from Contact_Contact_1 where "Bill To City"="Menlo Park"

NOTE:  You should not have any blank lines between each commands such as QueryType and CommandText. Connection information should be on one continuous line.

You can use Microsoft Access to create ad hoc reports. Using the Access Data Access Page Designer's drag-and-drop capabilities, you can create Web pages by selecting, dragging, and dropping Siebel OLE DB tables onto the Access form. The underlying OLE DB infrastructure writes the necessary information, and the newly created Web page accesses the Siebel virtual table data transparently.

To view Siebel data in Microsoft Access

1. Open Microsoft Access.
2. Choose File > New.
3. On the New dialog box, select the Data Access Page and click OK.

   The new Data Access Page dialog box displays.

4. Select Design View and click OK.

   The Data Link Properties dialog box displays.

5. Select the Provider tab.
6. Select Siebel OLE DB Provider from the picklist and click Next.
7. On the next page, fill in the parameters, including the Data Source and User name properties.
8. Click OK to save the changes.

   The Siebel OLE DB Provider login dialog appears.

9. Provide the password and click OK.

   You are presented with the designer and the Field List dialog box displaying the available integration objects.

10. Design and save this Web page.

    Access this Page View to review the results of the query from the OLE DB Provider.

Viewing Siebel Data Using Microsoft SQL Server Distributed Queries

In Microsoft SQL Server version 7.0, distributed queries enable SQL Server users to access data outside a SQL Server-based server, within either other servers running SQL Server or other data sources that expose an OLE DB interface. OLE DB provides a way to uniformly access tabular data from heterogeneous data sources.

A distributed query for the purpose of this document is any SELECT, INSERT, UPDATE, or DELETE statement that references tables and rowsets from one or more external OLE DB data sources.

A remote table is a table that is stored in an OLE DB data source and is external to the server running SQL Server executing the query. A distributed query accesses one or more remote tables.

Siebel OLE DB provider may be used as one of the data sources in the SQL 7.0 distributed query.

SELECT * FROM OPENROWSET('SiebelOLEDB.Provider.1','ConnectString';'UserId';'Password',Siebel OLE DB Provider query text)

For example:

SELECT * from OPENROWSET('SiebelOLE DB.Provider.1', 'somelhost,siebel,objmgr,w_name';

'SADMIN';'SADMIN',

SELECT "First Name, "Last Name" from Contact_Contact_1 where ("Job Title" = "Manager")

NOTE:  For more information about Microsoft SQL Server distributed queries, please refer to Microsoft SQL Server 7.0 documentation.

Database administrators (DBAs) find the OLE DB support in Siebel eAI useful for checking data integrity in Siebel applications and database-related tasks. With Siebel OLE DB Provider, table, row, and field information are displayed in the Microsoft SQL Analyzer for review and action.

To view Siebel data in SQL Analyzer

1. Start the Microsoft SQL Server Query Analyzer tool.
2. Connect to an SQL Server on which the Siebel OLE DB Provider has been installed.
3. Enter the SQL Server query text in the query window, as shown in the following illustration.
4. Enter a connect string similar to this (providing your actual ID, password, selection criteria, and so on):

   SELECT * from OPENROWSET('SiebelOLEDB.Provider.1','siebel://10.1.55.16/siebel/sseobjmgr/blitzlab32';
   
   'SADMIN';'db2',
   
   'select "Address ID","Created By","Bill To First Name" from test_table_1 where 'Bill To City'="Menlo Park"')