Using the OPENDATASOURCE function

You can access the flat (VDATA) view of a UNICOM Intelligence data source by using the Transact-SQL OPENDATASOURCE function in SQL Server.

The following example query returns all the data on the VDATA table from the The Short Drinks sample RDB database. The MR Init Category Names custom property has been set to 1 to return category names instead of category values.

[INSTALL_FOLDER] is the directory where you installed UNICOM Intelligence, such as C:\Program Files.

SELECT * FROM OPENDATASOURCE ( 'mrOleDB.Provider.2',
'Initial Catalog = [INSTALL_FOLDER]\IBM\SPSS\DataCollection\<version>\DDL\Data\mdd\short_drinks.mdd;
MR Init Category Names = 1' )...vdata

The three dots before vdata must be typed as shown. Note that because the data source and location have not been specified, the default data source defined in the .mdd file is used.

Execute this query by using the New Query feature in SQL Server Management Studio, the Query Analyzer tool in SQL Server, or a third party application that can connect to a SQL Server database, such as Microsoft Excel or Microsoft Access.

The following SQL Server query demonstrates how to use the OPENDATASOURCE function to join the following two tables:

▪The VDATA table for a UNICOM Intelligence Interviewer (RDB) data source that containing the response data for a survey.

▪The table that contains the sample data for the same survey. (In this example, the sample table is in the Participants database.)

The two are joined by the respondent.serial column on the VDATA table and the serial column on the sample table. The default data source in the MYFIRSTINTERVIEW.mdd file defines the UNICOM Intelligence Interviewer data source. Category names will be returned from the UNICOM Intelligence Interviewer data:

SELECT Responses.*, Sample.*
FROM OPENDATASOURCE ( 'mrOleDB.Provider.2',
'Initial Catalog = \\MyInterviewServer\SPSSMR_FMROOT\Shared\Projects\MYFIRSTINTERVIEW\MYFIRSTINTERVIEW.mdd;
MR Init Category Names = 1' )...vdata AS Responses,
Participants..MyFirstInterview AS Sample
WHERE Responses.[respondent.serial] = Sample.serial