Data Generation of Foreign keys -- failing (CTP 7)

I am trying to use the data generation plans to generate some test data. however, it seems that foreign keys columns are a problem. I keep getting an error

Error 420 The data type of column <my column> is Int. The selected data generator 'Foreign Key' with the selected output 'Output' does not generate data that can be converted to this type. Select a different data generator.

can anyone shed some light on a work around

Visual Studio Team System4

If you create a dgen plan against a normal int column and make changes to the generator properties, we will not automatically switch over to a foreign key generator when you add the foreign key relationship and a schema diff is triggered. This is to preserve settings you may have entered. However, you should be able to select the Generator column in the Column Details Window for the foreign key column in question and choose the appropriate generator from a drop down (in this case your only option should be Foreign Key). Once you set the generator to be Foreign Key, you should be able to populate.

The selected Generator is Foreign Key, i have no other option. This is the problem. some tables that have foreign keys (on the same column & to the same table) work fine. it's just some tables don't like the foreign key generator. it doesn't even attemtp to insert data, it just thinks it's an error and quits.

You're right i reversed the scenario, instead of going from fk to integer i went from integer to fk. However I am unable to repro the scenario you're describing. When I try it I am able to select from various integer generators. If you're interested, I can try working with your schema/dgen plan to see what the issue is. This requires some process but if you're interested please e-mail me at wiktork at microsoft dot com. (Please do not e-mail me any attachments yet). Meanwhile, I will try to reproduce the issue and see if I can find a suitable workaround. Note that you can always get around the issue if you recreate the dgen plan. The problem with that is that you will lose all your configuration.

There are a couple more work around that you could try in order to fix this. Before you try any of these be sure to backup your dgen file. I'm not 100% sure if these will work.

First work around:

Open the dgen plan using the vs xml editor. Find the <ColumnConfig> element that represents the column for which you cannot change the generator to the right type. A sub element of this will be the <UseDefaultGeneratorConfiguration> element. Its value should be false. Set it to true, and save the file. Reload it using the standard dgen designer, and see if the right generator gets set for the column. (e.g. integer instead of fk).

Second work around:

Open the dgen plan using the vs xml editor. Find the <ColumnConfig> element that represents the column for which you cannot change the generator. Delete this entire element. Save the dgen file, open it using the standard designer, and accept the schema changes. See if the right generator gets set for the column.

Let me know if any of these will work.

Rob -

Did Wiktor's suggestions get you unblocked If not, any additional information you can provide about the results you got when you tried the workarounds may help us come up with additional suggestions.

thanks,

We are still waiting on information from you. If we don’t hear from you in the next seven days, we will delete the thread. We do this to keep the system full of useful information for customers searching for answers and focus expert attention on active unanswered questions.”

Can you tell us what is the Column Type the foreignkey column is referencing from Is it also Int type or other type

Thanks,

I have the same problem described above. There is a self-referencing constraint on the table I wish to generate data. I created the data generation plan since adding the constraint, but it gives the error "The selected data generator 'Foreign Key' with the selected output 'Output' does not generate data that can be converted to this type. Select a different data generator."

I cannot select a different generator, wich is 'Foreign Key'/'Output'. I tried editing the xml as above but it didn't help.

Is there any other work arounds you can think of

I have fixed the problem in my case.

The columns were both of type int but the primary key that I was referencing was "IDENTITY(1,1)", I'm not sure what this syntax means but when I changed it to just "IDENTITY" the data generation worked without any error.

Thank you for your information.

I have just logged this issue and notified the team. We will investigate and fix this problem.

So the current workaround is to not make the primary key column IDENTITY.

Please let me know if you have any more questions.