Troubleshooting Data Generation Issues

Статья
02/04/2013

You might experience the following issues when you work with data generation plans:

Data Generation Fails When Check Constraints Are Violated
Cannot Assign Regular Expression Data Generator to User-Defined Type Columns
Cannot Generate Data for Tables with Delete Triggers
Issues with Data Bound Generators
Cannot Generate Data for SPARSE Columns

Data Generation Fails When Check Constraints Are Violated

When you generate data for a column that has a check constraint, the operation fails if data is generated that violates the constraint. You can use the Data Generation Preview window to see whether the data violates the constraint before you run the plan. For more information, see How to: Preview a Data Generation Plan.

To fix this problem, you must use one of the following methods:

Use the Min and Max properties to control how the data is generated. For example, suppose that you have a table that has two columns named StartDate and EndDate, and a check constraint that requires StartDate to precede EndDate. You can set the Max property of the StartDate column to a value that is less than the Min property of the EndDate column to achieve your goal. For more information, see Specify Details of Data Generation for a Column.
Use the data bound generator. For more information, see The Data Bound Generator.
Use a custom data generator. For more information, see Generate Specialized Test Data with a Custom Data Generator.

Cannot Assign Regular Expression Data Generator to User-Defined Type Columns with Unique Constraints

When you try to assign a data generator to a column that has a unique constraint, the list of available generators displays only generators that can produce unique values. Because the Regular Expression generator is not guaranteed to produce unique values, you cannot use it to fill a user-defined type column that has a unique constraint.

To generate data for a user-defined type column with a unique constraint, you must use the data bound generator or a custom data generator. For more information, see The Data Bound Generator and Generate Specialized Test Data with a Custom Data Generator.

Cannot Generate Data for Tables with Delete Triggers

Some tables have triggers that prevent you from deleting rows in those tables. If you try to delete rows from such a table, the trigger rolls back the delete operation. You cannot generate data normally for such a table or for a table that references such a table. For example, you cannot generate data normally for the Employee table in the AdventureWorks database.

To generate data for such a table, use one of the following methods:

Disable or delete the trigger, run the data generation plan, and re-enable or re-add the trigger.
Run the data generation plan. When you are prompted to clear the contents of the tables before new rows are inserted, click No. The new data that you generate is added to any existing data in the table so that the delete trigger does not occur. This method can cause unpredictable results, and errors might appear if any one of the columns has a unique constraint.

Issues with Data Bound Generators

If data generation fails for one or more columns for which you have specified a data bound generator, the Generator Output column in the Column Details window displays either "No Output" or "No Coercible Output". The following sections describe reasons for this kind of data generation failure.

No Output

If the Generator Output column displays "No Output", the SELECT statement that you specified in the Select Query property is probably incorrect. Because the SELECT statement can be difficult to see in the Properties window, you might want to copy and paste the statement into the Transact-SQL editor to verify whether the statement is correct.

No Coercible Output

If the Generator Output column displays "No Coercible Output", the data type that the SELECT statement returns cannot be coerced to the data type for the column for which you are generating data. For example, if you want to generate data for a column of type INT, the following SELECT statement would cause "No Coercible Output" to appear:

SELECT RAND() * (column1 - column2) AS Column1 FROM Table1

You must update the SELECT statement to return data that is coercible to INT, as follows:

SELECT CAST(RAND() * (column1 - column2) AS INT) AS Column1 FROM Table1

Cannot Generate Data for SPARSE Columns

When you try to generate data for a table that contains one or more SPARSE columns and a COLUMN_SET, the following error might appear: "Data generation failed because of the following exception: The given ColumnMapping does not match up with any column in the source or destination."

This might occur, for example, if you try to generate data for the following table:

CREATE TABLE [dbo].[SparseTable]
(
ID INT PRIMARY KEY,
C1 varchar(20) SPARSE NULL, 
C2 smallint SPARSE NULL,
C3 varchar(20) SPARSE NULL,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)

To generate data for a table that contains SPARSE columns and a COLUMN_SET, you must either change the preferred method of data insertion to "SqlV1CompatibilitySink" or you must use a custom data generator.

If you use the "SqlV1CompatibilitySink", data generation might be slow for large tables.

For more information about how to change the preferred method of data insertion, see How to: Specify Defaults and Options for Data Generators. For more information about how you can create and deploy a custom data generator, see Generate Specialized Test Data with a Custom Data Generator.

Поделиться через