"Scott M" <scott@nospam.com> wrote in message
news:ezl3f2saFHA.3120@TK2MSFTNGP12.phx.gbl...
> Okay, I have a problem that I need some suggestions with.
>
> I have a 30 Gig database that I need to sync across a T1 every night.
> This database is a data dump of our billing system data, the tables are
> dropped ad re-created every night.
>
> I can do a very long and laborious process on the server of creating
> tables with the primary keys from each table and a binary checksum of a
> row, then complaining it to a copy of the database from the day before.
> Then I generate SQL statements for the updates (Actually deletes and
> inserts). All of this on the source server.
>
> I'd like to compact this down using a DTS package on the destination side,
> however I can't use a dynamic query in the "openRowSet" command to get
> only the row that I want.
>
> Here is what I'm doing (Just for one table, I'll have data driven steps
> for each table in my DB)
>
> insert into @tempChecker
> select BSum, chgNo from openquery(EMBAN2, 'Select BINARY_CHECKSUM(*) BSum,
> chgno from charge_t')
>
> Declare Charge_t_Cursor Cursor
> FOR
> select
> a.chgno
> from
> (select
> BSum,
> chgno
> from @tempChecker
> ) a
> where
> not exists
> (
> select 'x'
> from charge_t b
> where a.chgno = b.chgno and a.BSum <> BINARY_CHECKSUM(*)
> )
>
> Once I get all the chgno's (The key in the table) I want to only pull back
> the data that has changes. So I have this (Which I know I can't do put
> you get the idea)
>
> Open Charge_t_Cursor
>
> Fetch next from Charge_t_cursor into @ChgNo
>
> While @@FETCH_STATUS = 0
> BEGIN
> insert into @ChargeT select *
> from OPENROWSET(EMBAN2, 'SELECT * FROM charge_t WHERE chgno = ' +
> cast(isnull(@ChgNo, 0) as varchar)))
>
> Fetch next from Charge_t_cursor into @ChgNo
> END
>
> Close Charge_t_cursor
> deallocate Charge_t_cursor
>
> select * from @ChargeT
>
> this way I'm only pulling over the wire the data that is updated and it
> will be in a nice, compact binary format. ANY help would be VERY
> appreciated!!! Thanks much!
>
> Scott

"Peter Nolan" <peter@peternolan.com> wrote in message
news:1119366628.053264.191240@z14g2000cwz.googlegroups.com...
> Hi Scott,
> maybe I'm not 'with it' but why do you want to move 30GB of data
> between machines just to keep 2 databases in sync?
>
> Do you mean you have two databases being updated and that you then need
> BOTH databases to be the same after the syncing process?
>
> Or do you mean you have one database being updated and you need to get
> the updates for that one across to the other?
>
> If the second one, and you have absolutely no way to get the changes
> from the database you can always do delta file generation.....that is,
> dump the tables and compare today vs yesterday, detect the changes and
> then send the delta file to the second machine....only a small number
> of rows are likely to have changes unless someone is updating lots of
> rows for no good reason....I have publised free tools to do this sort
> of thing on windows as files to get far better performance than doing
> it at a database level.....write to me at peter@peternolan.com if you
> want to try them......
>
> Best Regards
>
> Peter
>