Troubleshooting Data Generation Issues

  • Article

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

  • Cannot Open Data Generation Plan

  • 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 Open Data Generation Plan

If a data generator that is assigned to a column in an existing data generation plan cannot be found, you cannot open the plan, and the following error message appears:

Failed to load FileName.dgen. Cannot find an available generator with type GeneratorName. Please fix the error by manually editing and reloading the dgen file.

The following are some possible causes and resolutions of this error:

  • The name of the class of the data generator has changed. If only the name has changed, you can open the .dgen file in a text editor and change the XML manually. You must change the name of the data generator for the affected columns to the new name.

  • The data generator has been deprecated. You can open the .dgen file in a text editor and change the XML manually. In this case, you should to remove the complete <ColumnConfig> element (and all sub elements) from the XML for all affected columns. Open the data generation plan in Visual Studio and accept the schema changes. The default data generators are assigned to the affected columns. You must also reselect the affected columns to include them in the data generation plan. For more information, see How to: Specify Columns for Data Generation.

  • The entry for the data generator is removed from the extensions.xml file. You must change the extensions.xml file and add an <extensions> element for the data generator. You must close and reopen Visual Studio for the change to take effect. 

  • The .dll file and the extensions.xml file are out of synchronization. The data generator is not present in the .dll file that is defined in the extensions.xml file. You must redeploy the correct version of the assembly and the Extensions.xml file to fix the issue. You must close and reopen Visual Studio for the change to take effect.

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 that are 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 Specifying 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 Creating Custom Generators.

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 Creating Custom Generators.

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 on the AdventureWorks database.

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

  • Delete the trigger, run the data generation plan, and 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 type 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 (T-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

See Also

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Generating Data with Data Generators
Transact-SQL Reference (Transact-SQL)