sql server dts: Hello to everyone.
I need to synchronize data between servers, where the source is SQL Server
Express 2005, and the the server is SQL Server 2000.
I´m using VB.NET, and I linked the server, and then pass the information to
the server, but here is my problem:

TABLE CUSTOMERS
ID CUSTOMER (IDENTITY)
CUSTOMER NAME
TERMINAL ID (unique for each terminal)

TABLE SALES
ID SALES (IDENTITY)
ID CUSTOMER (relation whit table customers)
TOTAL
TERMINAL ID (unique for each terminal)

The problem is that, the server that recieves data, also is in use, and data
is inserted constantly. The source server, is used only when internet goes
offline.
So, users uses from different locations the server SQL Server 2000, and they
insert data, and in the case that one terminal goes offline, they work on
the sql server 2005 express, and when internet goes online, the data should
be transfered.
I have the terminal ID field, which is unique to each terminal, but I dont
know how to make the INSERT QUERY to select only the data that is not in the
server.
Thank you !
I hope I was clear.

[quoted text, click to view]

Hi,

I am hoping that you have left out a fairly substantial number of
columns from the table definitions you've supplied (the most glaring
ommision is that of transaction datetime!). Also, I'm assuming that
the IDENTITY columns on the 2 systems are not in sync, so using these
to determine which rows are missing is not an option...

In order to insert only missing rows, you need to have a unique key to
identify the rows accross the 2 terminals - as this does not appear to
exist in your system I would recommend the following:

1) Add datetime audit columns to all tables on both databases and
triggers on INSERT and UPDATE to keep an accurate record of when a row
was inserted or last changed. This will allow you to select rows from
either database that does not exist in the other using the datetime as
a filter. NOTE: This will require that the clocks on both servers
are syncronized.
2) Clear out the data on the backup system each time it is loaded to
the main database. So as part of your syncronization you clear out
(delete or archive) all the data in the SQL 2005 database after it has
been loaded to SQL 2000. This has the added benefit of keeping the
volumes in the backup database to a minimum.
3) Redesign your database to include a valid unique key accross both
systems. Using ID's generated by the application or GUIDs instead of
IDENTITY columns as your ID's would allow you more flexibility in
tracing data and synchronizing across databases.

Good luck!
J