Transferring data from a relational MR database (RDB)

Case data that is stored in a relational MR database is typically used with metadata in an .mdd file. When the case data is collected using UNICOM Intelligence software, the .mdd file will typically contain versions, which record any changes to the content of the questionnaire. Typically, when the metadata changes (for example, when variables or categories are added or deleted) a new version is created and when the changes are complete, the version is locked. For more information, see Understanding versions.

For information about exporting UNICOM Intelligence Interviewer - Server data, see Working with UNICOM Intelligence Interviewer - Server data. For a list of sample DMS files that have been designed specifically for use with UNICOM Intelligence Interviewer - Server data, see Sample DMS files for exporting UNICOM Intelligence Interviewer data.

In the connection string in the InputDataSource section, specify mrRdbDsc2 for the Data Source property and specify the OLE DB connection string for the Location property. For more information, see Connecting to a relational MR database using RDB DSC 2. You must also specify the MR Init Project connection property, and make sure that the Initial Catalog property is specified before the Location property:

InputDatasource(myInputDataSource, "My input data source")
ConnectionString = "Provider=mrOleDB.Provider.2; _
  Data Source=mrRdbDsc2; _
  Initial Catalog=<Name and location of .mdd file>; _
  Location=<OLE DB connection string>; _
  MR Init Project=<Name of project>"
End InputDatasource

You do not need to specify the Location and MR Init Project connection properties if the details are the same as those defined in the default DataSource object in the .mdd file. For example, if you are transferring UNICOM Intelligence Interviewer - Server data using the .mdd file in the FMRoot\Shared folder, these details are generally stored in the default DataSource in the .mdd file.

If you are using text substitution to replace text in the OLE DB connection string with your own text strings, you must enclose the value of the Location property in single quotes (') for the substitution to work correctly. There is an example of this in the sample include file RDBInput.dms:

InputDatasource(myInputDataSource, "RDBInput.dms")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrRdbDsc2; _
    Initial Catalog=" + MyMddFile + "; _
    Location=’Provider=MSOLEDBSQL.1;Integrated
      Security=SSPI;Persist Security Info=False;Initial
      Catalog=" + MyDatabase +";Data Source=" + MyServer + "’; _
      MR Init Project=" + MyProject
End InputDatasource

#define MyDatabase "SavToRdb"
#define MyServer "Localhost"

#Include ".\Include\RDBInput.dms"

InputDatasource(myInputDataSource, "My input data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrRdbDsc2; _
    Location="Provider=MSOLEDBSQL.1; _
      Integrated Security=SSPI; _
      Persist Security Info=False; _
      Initial Catalog=short_drinks; _
      Data Source=LocalHost"; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection
      \6\DDL\Data\Mdd\short_drinks.mdd; _
      MR Init Project=short_drinks; _
      MR Init MDM Version={..}"
End InputDatasource

OutputDataSource(Output, "My output data source")
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrSavDsc; _
    Location=C:\Program Files\IBM\SPSS\DataCollection\7\
      DDL\Output\RDBToSav.sav"
      MetaDataOutputName = "C:\Program Files\
          IBM\SPSS\DataCollection\7\DDL\Output\RDBToSav.mdd"
End OutputDataSource

A similar example is included as a sample DMS file called RDBToSav.dms, which is in the UNICOM Intelligence Developer Documentation Library. For more information, see “Sample DMS files” on page 409.

There is a known problem (mrBug00015444) that can affect DMS files that use RDB DSC 2 to read the input data and have an update query in the InputDataSource section. This problem can cause the update query to fail. You can avoid this problem by using the Favor Memory option, as shown in this example:

InputDatasource(mrInterviewData)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
  Data Source=mrRdbDsc2; _
    Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\
      6\DDL\Data\Mdd\short_drinks.mdd; _
      Location="Provider=MSOLEDBSQL.1; _
        Integrated Security=SSPI; _
        Persist Security Info=False; _
        Initial Catalog=short_drinks; _
        Data Source=LocalHost"; _
      MR Init Project=short_drinks; _
      MR Init Custom=FavorMemory"
    UpdateQuery = "UPDATE VDATA SET Respondent.Origin.Other =
      ’xxx’ _
      WHERE Respondent.Serial < 11"
End InputDatasource