Ignoring columns at Syncronisation

Brian Donahue

Hi Paul,

Thanks for your post. You can only ignore columns at compare-time, and
those settings filter through at synchronization time. You shouldn't be
allowed to ignore the primary key column, however, unless you choose another
unique index as a comparison key.

I'm a bit confused here because I don't understand how Data Compare
would mark a row as missing and create a situation where you'd wind up with
a constraint violation because of a duplicate value when Data Compare has
found that the value doesn't exist in the target database!

Regards,

Brian Donahue
Red Gate Technical Support

"Paul McNally" <paulm@vesereo.com> wrote in message
news:S7hVTqefEHA.1620@server53...
> Is there a way to ignore a column at syncronisation?
>
> I'm using identity columns throughout my DB. The identity column is part
> of the primary key. The comparision is correctly detecting new rows in
> the source DB.
>
> The problem is that the synchronisation is inserting the identity values
> which already exist in the target database. Is there a way to tell it to
> ignore these columns when it synchronises. The INSERT should still work
> as the identity columns will default to the next number, and I won't get
> constraint violations from duplicate identity values.

Brian Donahue

Hi Paul,

Does it work the way that you want if you exclude the TableId column
from the data comparison?

Regards,

Brian Donahue
Red Gate Technical Support

"Paul McNally" <paulm@vesereo.com> wrote in message
news:mmIXD7mfEHA.2864@server53...
> G'day Brian,
>
> What we are doing is upgrading a production database from development.
> In our development database we have a lot of default data rows which are
> indicated by setting a CompanyId column to -1. On upgrade we want to
> replace any CompanyId = -1 rows with those from the development database.
>
> For example a table in our development DB may look like this...
>
> CompanyId TableId Data
> (identity)
> -1 1 Our data
> -1 2 More Data
> -1 3 Still more data
>
> And the table in the production database is...
>
> CompanyId TableId Data
> (identity)
> -1 1 Our data
> 1 2 Our data
> 2 3 Our data
>
> To update we delete any rows in the production DB with CompanyId = -1.
> Then we do a data compare and it correctly picks up all the development
> DB rows as being missing (the PK is CompanyId, TableId). But when we
> sync the data the production DB looks like this...
>
> CompanyId TableId Data
> (identity)
> 1 2 Our data
> 2 3 Our data
> -1 1 Our data
> -1 2 More Data
> -1 3 Still more data
>
> This gives us a contraint violation as soon as we try to access it
> because of the duplicate TableId values. What I would like is this...
>
> CompanyId TableId Data
> (identity)
> 1 2 Our data
> 2 3 Our data
> -1 4 Our data
> -1 5 More Data
> -1 6 Still more data
>
> Which I think would be the result if the INSERT Data Compare used to add
> the new rows didn't include the TableId identity column.
>
> Regards,
> Paul.
>
> > Hi Paul,
> >
> > Thanks for your post. You can only ignore columns at compare-time,
and
> > those settings filter through at synchronization time. You shouldn't be
> > allowed to ignore the primary key column, however, unless you choose
another
> > unique index as a comparison key.
> >
> > I'm a bit confused here because I don't understand how Data Compare
> > would mark a row as missing and create a situation where you'd wind up
with
> > a constraint violation because of a duplicate value when Data Compare
has
> > found that the value doesn't exist in the target database!
> >
> > Regards,
> >
> > Brian Donahue
> > Red Gate Technical Support
> >
> > "Paul McNally" <paulm@vesereo.com> wrote in message
> > news:S7hVTqefEHA.1620@server53...
> >
> >>Is there a way to ignore a column at syncronisation?
> >>
> >>I'm using identity columns throughout my DB. The identity column is part
> >>of the primary key. The comparision is correctly detecting new rows in
> >>the source DB.
> >>
> >>The problem is that the synchronisation is inserting the identity values
> >>which already exist in the target database. Is there a way to tell it to
> >>ignore these columns when it synchronises. The INSERT should still work
> >>as the identity columns will default to the next number, and I won't get
> >>constraint violations from duplicate identity values.
> >
> >
> >

Brian Donahue

Hi Paul,

Okay, I think I've finally got the point. The problem is that there is
an identity column on TableId and you want the identity value to increment
rather than duplicating the TableId from the source database's table. The
reason that this doesn't work is that Data Compare tries to make a
carbon-copy of the table by setting IDENTITY_INSERT ON and 'forcing' the
destination table to have the same row identifiers as the source. In this
case, Data Compare isn't going to work because it's always going to set
IDENTITY_INSERT ON and copy the primary key columns' values from the source
to the destination.


Kind Regards,

Brian Donahue
Red Gate Technical Support

"Paul McNally" <paulm@vesereo.com> wrote in message
news:MwyLwuEgEHA.1228@server53...
> G'day Brian,
>
> No it doesn't work, because the PK is TableId and CompanyId, even
> excluding the TableId column it detects that the 3 columns are missing
> from the target DB. That is correct because we've removed the CompanyId
> = -1 rows from the target DB, and we want it to insert them.
>
> The problem is in the synchronise SQL script it creates, which is
> something like this...
>
> BEGIN TRANSACTION
> SET IDENTITY_INSERT [dbo]. ON
> INSERT INTO [dbo]. ([CompanyId], [TableId], [Data]) VALUES (-1,
> 4, 'Our data')
> INSERT INTO [dbo]. ([CompanyId], [TableId], [Data]) VALUES (-1,
> 5, 'More data')
> INSERT INTO [dbo]. ([CompanyId], [TableId], [Data]) VALUES (-1,
> 6, 'Even more data')
> SET IDENTITY_INSERT [dbo]. OFF
> COMMIT TRANSACTION
>
> Even though there are already rows in the table with TableId's of 4, 5
> and 6, hence the constraint violation. What I want it to do is create
> script like this...
>
> BEGIN TRANSACTION
> INSERT INTO [dbo]. ([CompanyId], [Data]) VALUES (-1, 'Our data')
> INSERT INTO [dbo]. ([CompanyId], [Data]) VALUES (-1, 'More data')
> INSERT INTO [dbo]. ([CompanyId], [Data]) VALUES (-1, 'Even more
> data')
> COMMIT TRANSACTION
>
> It will still work, because TableId is an identity field, SQL Server
> will assign values that don't clash with the rows which are already there.
>
> Regards,
> Paul.
>
> > Hi Paul,
> >
> > Does it work the way that you want if you exclude the TableId column
> > from the data comparison?
> >
> > Regards,
> >
> > Brian Donahue
> > Red Gate Technical Support

Brian Donahue

Hi Paul,

It doesn't look likely -- for the simple reason that in order for Data
Compare to do what it's supposed to, make tables data appear identical, it
needs to preserve the identities of the source table.

Regards,

Brian Donahue
Red Gate Technical Support


"Paul McNally" <> wrote in message news:4129ADC9.6060900@vesereo.com...
> Brian,
>
> Should I assume from the lack of response that we're not going to get an
> option to skip the identity fields when data compare does these inserts?
>
> Paul.
>
>
> > Hi Paul,
> >
> > Okay, I think I've finally got the point. The problem is that there
is
> > an identity column on TableId and you want the identity value to
increment
> > rather than duplicating the TableId from the source database's table.
The
> > reason that this doesn't work is that Data Compare tries to make a
> > carbon-copy of the table by setting IDENTITY_INSERT ON and 'forcing' the
> > destination table to have the same row identifiers as the source. In
this
> > case, Data Compare isn't going to work because it's always going to set
> > IDENTITY_INSERT ON and copy the primary key columns' values from the
source
> > to the destination.
> >
> >
> > Kind Regards,
> >
> > Brian Donahue
> > Red Gate Technical Support