Excluding fields from comparisson

TheBatA

Hi!
I have two databases which I need to compare.
First one is the table from which I have to make second table.
First table has about 82 tables, and second is smaller, it has about 47 tables. Some of fields in second db are excluded.
When I do the standard comparison (by "standard comparison" I mean comparison about which I've posted before on this forum (http://www.red-gate.com/messageboard/viewtopic.php?t=5684)) and I make package for updating database (using SQL Package), I get this error in middle of execution:

Cannot insert the value NULL into column 'ProdNr', table 'eKatTest.dbo.tmp_rg_xx_050_Criteria_Table'; column does not allow nulls. INSERT fails.

This field 'ProdNr' does exist in '050_Criteria_Table' in first database and it's a primary key, but it's excluded from second database.
How can I avoid this error?
I am using theese sql options in comparison session:

session.Options.SqlOptions |= SqlOptions.DropConstraintsAndIndexes | SqlOptions.UseTransactions | SqlOptions.DisableKeys;

TheBatA

I'm getting a little annoyed with this error I keep getting while executing package I've created for updating this second table:

Cannot insert the value NULL into column 'ProdNr', table 'eKatTest.dbo.tmp_rg_xx_050_Criteria_Table'; column does not allow nulls. INSERT fails.

I have excluded this 050_Criteria_Table from comparison, but i keep getting this error.
I tried to map fields manually:

tm.FieldMappings.Clear();
foreach (Field f in tm.Obj2.Fields)
{
          FieldMapping film = new FieldMapping(f, tm.Obj1.Fields[f.Name], FieldMappingStatus.Success);
          MessageBox.Show(tm.Obj2.Name, f.Name + tm.Obj1.Fields[f.Name].Name);
          tm.FieldMappings.Add(film);
}

Why does this happen?

Andras B

TheBatA wrote:

I'm getting a little annoyed with this error I keep getting while executing package I've created for updating this second table:

Cannot insert the value NULL into column 'ProdNr', table 'eKatTest.dbo.tmp_rg_xx_050_Criteria_Table'; column does not allow nulls. INSERT fails.

I have excluded this 050_Criteria_Table from comparison, but i keep getting this error.
I tried to map fields manually:

tm.FieldMappings.Clear();
foreach (Field f in tm.Obj2.Fields)
{
          FieldMapping film = new FieldMapping(f, tm.Obj1.Fields[f.Name], FieldMappingStatus.Success);
          MessageBox.Show(tm.Obj2.Name, f.Name + tm.Obj1.Fields[f.Name].Name);
          tm.FieldMappings.Add(film);
}

Why does this happen?

Could you post the table definition of the above table or just send it to me in a pm. Concerning why this is happening: SQL Compare (the part that is handling the schema differences) is trying to rebuild your table (hence the temporary table tmp_rg_...) There could be many reasons for rebuilding the table. Among these reasons are changes to identity properties, filegroups, partitioning, ... . Once I can see the table schema, I'll be able to tell you why the table is rebuilt. (You could use SQL Compare to get the table schema, it will include all the dependent objects like foreign keys, bindings). Why this fails? There may be a new column that needs to be added, and if this column does not allow nulls, AND it does not contain a default, the table cannot be populated. Once again, seeing the schema would help to identify the exact cause. A workaround could be to set up a default for the relevant non nullable columns.

Regards,
Andras