Transferring data to a relational MR database (RDB)

Professional > Data management scripting > Transferring data using a DMS file > Writing data > Transferring data to a relational MR database (RDB)

This topic provides some notes on using a DMS file to transfer case data to a relational MR database. The DMS file uses the Relational MR database (RDB) CDSC to write the data to the database.

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.

Writing to a new database

Before you can transfer case data to a new database, you need to create the SQL Server database. See the instructions later in this topic.

Writing to an existing database

Relational MR database CDSC can write new data to an existing database. However, except when you are using the UseInputAsOutput option, existing records are not updated and you will get an error if you attempt to write records that already exist in the database--that is, records for which the serial number (stored in the Respondent.Serial system variable) is already in use in the database. However, if necessary you can manipulate the serial number in the OnNextCase Event section--for example, by adding 1000 to its value. Alternatively, you could clear the Respondent.Serial variable, because if it is blank, Relational MR Database CDSC will automatically generate new serial numbers when writing the records.

If the structure of the data has changed, the CDSC will update the structure in the existing database whenever possible. However, this is not possible if the data type of any of the variables has changed. For example, you will get an error (typically “Type Mismatch” or “Invalid Index”) if you attempt to export a categorical variable called age to a database that already contains a numeric variable called age.

Setting up weighting

Relational MR Database CDSC can update existing records and so you can use the Weight component to set up weighting in the database. For an example of doing this, see Setting up weighting in a DMS file.

How do I specify an export using RDB DSC 2?

In the connection string in the OutputDataSource 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” in the UNICOM Intelligence Developer Documentation Library. You must also specify the MR Init Project connection property and make sure that the Initial Catalog property is set to an empty string and is specified before the Location property:

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

Using text substitution in the OLE DB connection string

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 RDBOutput.dms:

OutputDataSource(RDBOutput)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrRdbDsc2; _
    Initial Catalog=’’; _
    Location=’Provider=SQLOLEDB.1;Integrated
        Security=SSPI;Persist Security Info=False;Initial
        Catalog=" + Target +
         ";Data Source=LocalHost’; _
    MR Init Project=" + Target
  MetaDataOutputName = " [INSTALL_FOLDER]\IBM\SPSS\
    DataCollection\7\DDL\Output\" + Target + ".mdd"
End OutputDataSource

RDBOutput.dms is used by the sample file SavToRDB.dms, which includes the following code:

#define Target "SavToRdb"
#Include ".\Include\RdbOutput.dms"

By enclosing the value of the Location property in single quotes, the value of Target will be correctly inserted into the OLE DB connection string.

How do I create a new SQL Server database?

To create a new SQL Server database, you need SQL Server Client Tools. There are several ways of creating a new database, depending on the version of the SQL Server Client Tools that you are using:

1. Using SQL Server Management Studio

1 Open SQL Server Management Studio.

2 In the Object Explorer pane, expand the server on which you want to create the database.

3 Right‑click the Databases node, and then click New Database.

The New Database dialog box opens.

4 Enter a Database Name, and then click OK.

The database is created.

2. Using the sqlcmd command prompt utility

In SQL Server, isql has been replaced by sqlcmd. However, for most purposes, both utilities share the same command line parameters. Therefore, the command to create a database using sqlcmd is almost the same as when using isql:

sqlcmd -E -d Master -Q "Create Database NewMuseum" -S LocalHost

To list all the sqlcmd parameters, use the following command:

sqlcmd -?

Example 1: Using the default Favor Speed option

This example shows a DMS file that transfers case data from a Data Collection Data File (.ddf) to a relational MR database, using RDB DSC 2 and its default Favor Speed option. Before you run this example, you need to create the NewMuseum database and, if necessary, change the connection string in the OutputDataSource section to reflect the name of the server you are using.

InputDataSource(myInputDataSource)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrDataFileDsc; _
    Location=C:\Program       Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection
        File\museum.ddf; _
    Initial Catalog=C:\Program       Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection
        File\museum.mdd"
  SelectQuery = "SELECT * FROM VDATA WHERE Respondent.Serial <     101"
End InputDataSource
OutputDataSource(myOutputDataSource)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrRdbDsc2; _
    Initial Catalog=’’; _
    Location=’Provider=SQLOLEDB.1; _
      Integrated Security=SSPI; _
      Persist Security Info=False; _
      Initial Catalog=NewMuseum; _
      Data Source=LocalHost’; _
    MR Init Project=NewMuseum"
  MetaDataOutputName = "C:\Program     Files\IBM\SPSS\DataCollection\7\DDL\Output\NewMuseum.mdd"
End OutputDataSource

Note This example is provided as a sample DMS file (called DDFToRDB.dms) that is installed with the UNICOM Intelligence Developer Documentation Library. For an example that uses the ShellExecute function to call an MS-DOS batch file to create the database, see the SavToRDB.dms sample. For more information, see “Sample DMS files” on page 409.

Example 2. Using the Favor Memory option

There is a known problem (mrBug00015444) that can affect DMS files that use RDB DSC 2 to write the data. This problem can cause the transfer to fail. You can avoid this problem by using the Favor Memory option, as shown in this example:

OutputDataSource(Output)
  ConnectionString = "Provider=mrOleDB.Provider.2; _
    Data Source=mrRdbDsc2; _
    Initial Catalog=""; _
    Location="Provider=SQLOLEDB.1; _
      Integrated Security=SSPI; _
      Persist Security Info=False; _
      Initial Catalog=TargetData; _
      Data Source=LocalHost"; _
    MR Init Project=TargetData; _
    MR Init Custom=FavorMemory"
  MetaDataOutputName = "     [INSTALL_FOLDER]\IBM\SPSS\DataCollection\7\DDL\Output\
      TargetData.mdd"
End OutputDataSource

For more information, see Known problems in Relational MR Database CDSC.

Troubleshooting checklist

▪Did the SQL Server database exist before running the transfer?

▪Does the Data Source property in the string specify the server you are using correctly?

▪Have you specified the Initial Catalog connection property as an empty string and put it in front of the Location connection property?

▪Did you specify the project name (MR Init Project connection property) correctly?

▪Do the serial numbers in the data you are transferring conflict with serial numbers that already exist in the target database?

▪Has there been a change in the data type of any of the variables you are transferring? (This applies only if you are transferring to an existing RDB database.)

Requirements

UNICOM Intelligence Professional

SQL Server Client Tools