Data Model > Available DSCs > Relational MR database (RDB) CDSC > Known problems in Relational MR Database CDSC
 
Known problems in Relational MR Database CDSC
ECM00178359: Problems when switching loops from expanded to unexpanded (or vice versa) after a project has been activated
The following example DMS script's Metadata section resolves the problem of switching loops from expanded to unexpanded (or vice versa) after a project has been activated:
InputDatasource(myInputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrRdbDsc2; _
Initial Catalog=C:\Work\ROJECTX.mdd; _
Location="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=PROJECTX;Data Source=LocalHost"; _
MR Init Project=PROJECTX"
SelectQuery = "SELECT * from Hdata"
End InputDatasource

Metadata(en-US, Question, Label)
LoopQ "LoopQ" loop [0..2] fields -
(
Q1 "This is a question in the loop"
categorical [1..1]
{
A "A" exclusive,
B "B" exclusive,
C "C" exclusive,
D "D" exclusive,
E "E" exclusive
};

)expand;
End Metadata

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=PROJECTY; _
Data Source=LocalHost'; _
MR Init Project=PROJECTY"
MetaDataOutputName = "C:\Work\PROJECTY.mdd"
End OutputDataSource
ECM00135726: RDB DSC2 does not support unexpanded level variables under expanded level variables
Because the RDB DSC2 does not support unexpanded level variables under expanded level variables, another version of the household.mdd file and associated, backup relational database have been added to the UNICOM Intelligence Developer Documentation Library. These new files are installed to:
[INSTALL_FOLDER]\IBM\SPSS\DataCollection\7\DDL\Data\RDB
household.mdd
household.bak
The loop variable person in these new files have been updated from mlExpand to mlLevel in MDM2 Explorer.
ECM00178359: Issues when switching a loop from expanded to unexpanded (or vice versa) after a project is activated.
The following issues may arise after a project is activated:
Changing an unexpanded loop variable to an expanded loop variable results in the error:
Unable to convert unexpanded loop 'LOOP_NAME' to an expanded loop.
To convert the loop, create a new project and transfer the existing data to
the new database.
After changing an expanded loop variable to an unexpanded loop variable, data that is collected against the expanded loop variable is no longer visible.
The issue can be resolved by creating a new project and transferring the existing data to the new database. The following example provides steps for transferring case data from unexpanded loop variable to an expanded loop variable.
1 Create a new project (for example, ProjectX.mdd) with the following definition:
Metadata(en-GB, Question, Label)
  LoopQ "LoopQ" loop
    {
      X "X",
      Y "Y",
      Z "Z"
    } fields -
    (Q1 "This is a question in the loop"
       categorical [1..1]
       {A "A" exclusive,
        B "B" exclusive,
        C "C" exclusive,
        D "D" exclusive,
        E "E" exclusive
       };
    Q2 "This is another question in the loop"
    long;
    );
End Metadata
2 Activate the project in UNICOM Intelligence Interviewer, without the expand keyword at the end of the definition.
3 Conduct a survey against the new project (case data in HDATA).
4 Create and activate another project (for example, ProjectY.mdd) with the same metadata as ProjectX.mdd, but ensure that the expandkeyword is added at the end of the definition.
5 Run the following DMS script to transfer case data from ProjectX.mdd to ProjectY.mdd:
InputDatasource(myInputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2;_
Data Source=mrRdbDsc2;_
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\Interviewer Server\FMRoot\Master\ProjectX\ProjectX.mdd;_
Location="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ProjectX;Data Source=LocalHost";_
MR Init Project=ProjectX"
SelectQuery = "SELECT * FROM HDATA"
End InputDatasource

Metadata(en-US, Question, Label)
LoopQ "LoopQ" loop
{
X "X",
Y "Y",
Z "Z"
} fields -
(
Q1 "This is a question in the loop"
categorical [1..1]
{
A "A" exclusive,
B "B" exclusive,
C "C" exclusive,
D "D" exclusive,
E "E" exclusive
};

Q2 "This is another question in the loop" long;

)expand;
End Metadata

OutputDataSource(myOutputDataSource)
ConnectionString = "Provider=mrOleDB.Provider.2;_
Data Source=mrRdbDsc2;_
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\Interviewer Server\FMRoot\Master\ProjectY\ProjectY.mdd";_
Location='Provider=SQLOLEDB.1;_
Integrated Security=SSPI;_
Persist Security Info=False;_
Initial Catalog=ProjectY;_
Data Source=LocalHost';_
MR Init Project=ProjectY"
End OutputDataSource
6 Run DMQUERY against VDATA for ProjectY.mdd to flatten the data.
mrBug00014573: Transferring data from older databases without an .mdd file
The internal changes in the MDM in UNICOM Intelligence Data Model 2.7 mean that the MDM now creates the variables (VariableInstance objects) that relate to the same question or loop in a different order to previous versions of the UNICOM Intelligence Data Model.
This can cause data to be written to the wrong columns if you do not use an .mdd file when you transfer data from a relational MR database that was created with an earlier version of the UNICOM Intelligence Data Model. However, this problem can be avoided by using the .mdd file in the input data source connection.
mrBug00014594: Loss of data due to a change of data type in the .mdd file
This problem is not specific to the RDB DSC 2 and is actually caused by a problem in the MDM. The problem occurs because unfortunately the MDM does not give you an error if you create a new variable using the name of a variable that existed in an earlier version but which was of a different data type.
For example, suppose that in version 1, you create a categorical variable called Pets and then activate the project. This involves the UNICOM Intelligence Data Model synchronizing the case data with the metadata and creating a Pets column of type categorical in the case data. You then delete the Pets variable in version 2 and reactivate the project. Although you have deleted the variable from the current version of the metadata, the Pets column is still in the database. If in the next version you add a new variable called Pets, but make it a text variable, you can activate the project without getting an error. However, you can not write the text case data to the Pets column, because it is still of type categorical.
This problem would not have arisen if the new variable had a different name (such as NumberOfPets) or if it had the same data type as before. In addition this problem would not have arisen if you had not deleted the Pets variable in version 2, because an error would have occured when the project was activated.
Therefore, make sure that after the case data has been synchronized with the metadata (which happens during activation), you do not create a new variable using the name of a variable that was in an earlier version but which was of a different data type.
mrBug00015444: Update failure when using RDB DSC 2
When you use RDB DSC 2 to connect to an RDB database, SQL UPDATE statements may fail, particularly when inserting large amounts of data. A similar problem may also occur when an SQL DELETE statement is used to delete data that is stored in the OtherData table in a database that uses the older RDB DSC schema. Typical error messages when the failure occurs during an UPDATE statement are:
An error occurred updating a row: RDB Update failed: Timeout expired
- OR -
RDB Update failed: IDispatch error #3121
The solution to this problem is to use the Favor Memory option. Select Favor Memory by setting the MR Init Custom connection property to FavorMemory. For more information, see Connecting to a relational MR database using RDB DSC 2.
Note This problem does not occur when UNICOM Intelligence Interviewer updates data, because it always connects to RDB DSC 2 using the Favor Memory option.
mrBug00016885: RDB DSC 2 returns no rows at a lower level when only the LevelId column is bound
This problem occurs when you are performing queries on the HDATA hierarchical tables and a sub-select statement is used to retrieve data from a lower level but only the LevelId column is specified. For example, if you run the following query on the Short Drinks sample, you will find that no rows are returned for the frequenz grid:
SELECT Respondent.Serial, frequenz.(LevelId) FROM hdata
This occurs because RDB DSC 2 filters Null rows at lower levels and considers rows to be Null if all of the columns other than the LevelId are Null. Therefore when only the LevelId column is bound, RDB DSC 2 considers all of the child rows to be Null. However, if you bind another lower-level column, the rows are returned. For example, the following query returns the rows for the frequenz grid:
SELECT Respondent.Serial, frequenz.(LevelId, questi2) FROM hdata
For more information on SQL queries on HDATA, see Hierarchical SQL queries.
mrBug00039060: The Export Data activity in UNICOM Intelligence Interviewer exports less records than expected
This is usually caused by a deadlock on the UNICOM Intelligence Interviewer - Server Admin database, a problem that is silently ignored by the . In UNICOM Intelligence 4.0 and later, the Export Data activity connects to the UNICOM Intelligence Interviewer data using RDB DSC 2's Favor Memory option, which reduces the likelihood of deadlocks.
To resolve this problem in UNICOM Intelligence 3.5 and earlier, write a data management script (DMS) file to export the UNICOM Intelligence Interviewer data, and include the Favor Memory option in the connection string for the input data source. For more information about the Favor Memory option, see Connecting to a relational MR database using RDB DSC 2. For more information about writing a DMS script, see Transferring data from a relational MR database (RDB).
See also
Relational MR Database CDSC schema
Stored procedures
WHERE expressions supported natively
Relational MR database (RDB) CDSC