Errors and Change requests
Brian Donahue
Posts: 6,590 Bronze 1
Hello,
Thanks for writing Red Gate. I have done some testing on the foreign key
problem, and found that it works correctly, at least with one table, anyway.
The primary key constraint gets done before the foreign key as it should.
I'd like to see a script of your tables to see if there might be something I
miss.
The second comment would involve LOTS of storage and network usage on
the client side. Are you sure it wouldn't be better to DTS/backup/replicate
the data to a local database, and run SQL Data Compare against it?
Conditional compare counds like a good idea. We'll have to look into it.
Brian Donahue
Technical Support Engineer
Red Gate Software Ltd.
+44 870 1600 037
mailto:brian.donahue@red-gate.com
"Geir Ludvigsen" <geir.ludvigsen@consultit.no> wrote in message
news:CVM3fqS7CHA.1412@server53...
> First of all, the SQL Compare og Data Compare tools are incredible useful.
I
> use them synchronizing several development databases from "master" and
> "template" databases. I also use it to upgrade several databases in
> different environments, from development, to test, to production.
>
> ERRORS
> 1. If a table has a foreign key of type SAME TABLE, that means it
references
> a primary key value in the same table as the foreign key, the generated
> script doesn't script the inserts in correct order. I manually have to
move
> the insert for the "parent" row above the "child" rows. References between
> columns in different tables works correctly.
>
> CHANGE REQUESTS
> 1. With SQL Compare you can compare two database snapshots disconnected.
You
> can save a "stabile" version of a database to compare other databases to.
> But I cannot do the same with SQL Data Compare. For data synchronization I
> have to compare "on-line" to my unstabile development database. It would
> have been really nice if I could save the belonging data together with the
> snapshot of the stabile database.
>
> 2. We develop on a "master" database and this database has some basic data
> in it. We also have several customer databases which are copies of the
> "master", but can have additional customer specific data in them. To
> distinguish between data that are basic, customer specific, and production
> data, primary keys are INTs and are allocated in three different data
> series. Basic data from 0 to 4999, customer data from 5000 to 9999, and
> production data from 10000 and above. We have developed our own scripts
that
> clean up the Data Compare scripts. In this way we can remove data above
5000
> synchronizing from "master" to customer database, and remove data above
> 10000 synchronizing from a customer database on the development server to
> the test server. In addition we have scripted to always remove update
and/or
> delete commands on specific tables. That means that we insert new rows for
a
> table, but never update or delete them because they containt environment
> specific data and must be manually updated/deleted.
>
> Well, now to my request:
> * Ability to specify a WHERE clause for each table. We must be able to
save
> the WHERE clauses in a template or somewhere so we can use it for later
> comparisons.
> * Ability to exclude insert, update or delete commands for each table.
This
> we must also be able to save in the same template.
> It must be relatively easy to edit these templates.
>
> Geir Ludvigsen
>
>
Thanks for writing Red Gate. I have done some testing on the foreign key
problem, and found that it works correctly, at least with one table, anyway.
The primary key constraint gets done before the foreign key as it should.
I'd like to see a script of your tables to see if there might be something I
miss.
The second comment would involve LOTS of storage and network usage on
the client side. Are you sure it wouldn't be better to DTS/backup/replicate
the data to a local database, and run SQL Data Compare against it?
Conditional compare counds like a good idea. We'll have to look into it.
Brian Donahue
Technical Support Engineer
Red Gate Software Ltd.
+44 870 1600 037
mailto:brian.donahue@red-gate.com
"Geir Ludvigsen" <geir.ludvigsen@consultit.no> wrote in message
news:CVM3fqS7CHA.1412@server53...
> First of all, the SQL Compare og Data Compare tools are incredible useful.
I
> use them synchronizing several development databases from "master" and
> "template" databases. I also use it to upgrade several databases in
> different environments, from development, to test, to production.
>
> ERRORS
> 1. If a table has a foreign key of type SAME TABLE, that means it
references
> a primary key value in the same table as the foreign key, the generated
> script doesn't script the inserts in correct order. I manually have to
move
> the insert for the "parent" row above the "child" rows. References between
> columns in different tables works correctly.
>
> CHANGE REQUESTS
> 1. With SQL Compare you can compare two database snapshots disconnected.
You
> can save a "stabile" version of a database to compare other databases to.
> But I cannot do the same with SQL Data Compare. For data synchronization I
> have to compare "on-line" to my unstabile development database. It would
> have been really nice if I could save the belonging data together with the
> snapshot of the stabile database.
>
> 2. We develop on a "master" database and this database has some basic data
> in it. We also have several customer databases which are copies of the
> "master", but can have additional customer specific data in them. To
> distinguish between data that are basic, customer specific, and production
> data, primary keys are INTs and are allocated in three different data
> series. Basic data from 0 to 4999, customer data from 5000 to 9999, and
> production data from 10000 and above. We have developed our own scripts
that
> clean up the Data Compare scripts. In this way we can remove data above
5000
> synchronizing from "master" to customer database, and remove data above
> 10000 synchronizing from a customer database on the development server to
> the test server. In addition we have scripted to always remove update
and/or
> delete commands on specific tables. That means that we insert new rows for
a
> table, but never update or delete them because they containt environment
> specific data and must be manually updated/deleted.
>
> Well, now to my request:
> * Ability to specify a WHERE clause for each table. We must be able to
save
> the WHERE clauses in a template or somewhere so we can use it for later
> comparisons.
> * Ability to exclude insert, update or delete commands for each table.
This
> we must also be able to save in the same template.
> It must be relatively easy to edit these templates.
>
> Geir Ludvigsen
>
>
This discussion has been closed.
Comments
I have submitted your ideas for development to have a look at.
Thanks,
Brian Donahue
"Geir Ludvigsen" <geir.ludvigsen@consultit.no> wrote in message
news:Ua5QG1JEDHA.2456@server53...
> Thank you for your answer.
>
> 1. Foreign key problem with foreign keys of type SAME TABLE:
> You have to insert the "parent" row AFTER you have inserted the "child"
row.
> To do this, you cannot have the foreign key active. After you have
inserted
> the rows you can enable the foreign key. In our case, the table didn't
have
> the foreign key at first, we added it later. Example/steps:
> CREATE TABLE TestTbl (MyID INT NOT NULL, ParentID INT NULL)
> INSERT INTO TestTbl VALUES (1, 10)
> INSERT INTO TestTbl VALUES (2, 10)
> INSERT INTO TestTbl VALUES (10, NULL)
> -- Now add the foreign key constraint for ParentID of type SAME TABLE.....
> -- Then try to generate script with Data Compare, my guess is that the
order
> of inserts is incorrect. The correct order would be primary key values 10,
> 1, 2
>
> Regards,
> Geir Ludvigsen, Senior Consultant
> ConsultIT AS
> Trondheim, Norway
>
>
> "Brian Donahue (Red Gate)" <brian.donahue@red-gate.com> wrote in message
> news:0MWPkNh8CHA.1424@server53...
> > Hello,
> >
> > Thanks for writing Red Gate. I have done some testing on the foreign
> key
> > problem, and found that it works correctly, at least with one table,
> anyway.
> > The primary key constraint gets done before the foreign key as it
should.
> > I'd like to see a script of your tables to see if there might be
something
> I
> > miss.
> >
> > The second comment would involve LOTS of storage and network usage
on
> > the client side. Are you sure it wouldn't be better to
> DTS/backup/replicate
> > the data to a local database, and run SQL Data Compare against it?
> >
> > Conditional compare counds like a good idea. We'll have to look into
> it.
> >
> > Brian Donahue
> >
> > Technical Support Engineer
> >
> > Red Gate Software Ltd.
> >
> > +44 870 1600 037
> >
> > mailto:brian.donahue@red-gate.com
> >
> >
> >
> > "Geir Ludvigsen" <geir.ludvigsen@consultit.no> wrote in message
> > news:CVM3fqS7CHA.1412@server53...
> > > First of all, the SQL Compare og Data Compare tools are incredible
> useful.
> > I
> > > use them synchronizing several development databases from "master" and
> > > "template" databases. I also use it to upgrade several databases in
> > > different environments, from development, to test, to production.
> > >
> > > ERRORS
> > > 1. If a table has a foreign key of type SAME TABLE, that means it
> > references
> > > a primary key value in the same table as the foreign key, the
generated
> > > script doesn't script the inserts in correct order. I manually have to
> > move
> > > the insert for the "parent" row above the "child" rows. References
> between
> > > columns in different tables works correctly.
> > >
> > > CHANGE REQUESTS
> > > 1. With SQL Compare you can compare two database snapshots
disconnected.
> > You
> > > can save a "stabile" version of a database to compare other databases
> to.
> > > But I cannot do the same with SQL Data Compare. For data
synchronization
> I
> > > have to compare "on-line" to my unstabile development database. It
would
> > > have been really nice if I could save the belonging data together with
> the
> > > snapshot of the stabile database.
> > >
> > > 2. We develop on a "master" database and this database has some basic
> data
> > > in it. We also have several customer databases which are copies of the
> > > "master", but can have additional customer specific data in them. To
> > > distinguish between data that are basic, customer specific, and
> production
> > > data, primary keys are INTs and are allocated in three different data
> > > series. Basic data from 0 to 4999, customer data from 5000 to 9999,
and
> > > production data from 10000 and above. We have developed our own
scripts
> > that
> > > clean up the Data Compare scripts. In this way we can remove data
above
> > 5000
> > > synchronizing from "master" to customer database, and remove data
above
> > > 10000 synchronizing from a customer database on the development server
> to
> > > the test server. In addition we have scripted to always remove update
> > and/or
> > > delete commands on specific tables. That means that we insert new rows
> for
> > a
> > > table, but never update or delete them because they containt
environment
> > > specific data and must be manually updated/deleted.
> > >
> > > Well, now to my request:
> > > * Ability to specify a WHERE clause for each table. We must be able to
> > save
> > > the WHERE clauses in a template or somewhere so we can use it for
later
> > > comparisons.
> > > * Ability to exclude insert, update or delete commands for each table.
> > This
> > > we must also be able to save in the same template.
> > > It must be relatively easy to edit these templates.
> > >
> > > Geir Ludvigsen
> > >
> > >
> >
> >
>
>