My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :

- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database

So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.

Thanks

There are a number of ways to approach this

You could have an indicator on the source tables to say when this row was
added/updated. You then have a table with load dates and times and you can
then compare when you last loaded the reporting database with the
"LastUpdated" flag on the table.

You could have triggers on the Source tables which log operations. So say
you delete a row in a table at the source, the trigger may log

PrimaryKey Value
Operation Type (I,U,D)

This way you know which rows to retrieve from the source at load time. Also
the operation type eill tell you what to do with that row at the
destination.

You can also use Linked servers and this then becomes a matter of issuing
queries against the source and destination and taking approprate actions.

Just a few ways and there are others

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Resant" <resant_v (AT) yahoo (DOT) com> wrote

Your suggestion is more good than my idea. But there's some difficult
when I try to implement it in DTS.
I prepare a table to save LogTime, with structure :
DTSLog
(
DTSName,
StartTime,
Duration
EndTime AS (dateadd(hour,[duration],[starttime])
)
The problem is :
1. I've passing DTSLog contain to Global Variables with type Rowset
But how to use it as Output Parameter in Execute SQL Task, for
example to this query :
DELETE dbReport..DeliveryNotes
FROM dbReport..DeliveryNotes D1,
ServerA.db2.dbo.DeliveryNotes D2
WHERE D1.ReceivingID=D2.ReceivingID AND dtModified
BETWEEN ? AND ?
I get an error : Syntax error or access violation
2. I hear that parameter cannot used in query that have subquery. Is
that
true.

Please give some clue

Thanks

Your suggestion is more good than my idea. But there's some difficult
when I try to implement it in DTS.
I prepare a table to save LogTime, with structure :
DTSLog
(
DTSName,
StartTime,
Duration
EndTime AS (dateadd(hour,[duration],[starttime])
)
The problem is :
1. I've passing DTSLog contain to Global Variables with type Rowset
But how to use it as Output Parameter in Execute SQL Task, for
example to this query :
DELETE dbReport..DeliveryNotes
FROM dbReport..DeliveryNotes D1,
ServerA.db2.dbo.DeliveryNotes D2
WHERE D1.ReceivingID=D2.ReceivingID AND dtModified
BETWEEN ? AND ?
I get an error : Syntax error or access violation
2. I hear that parameter cannot used in query that have subquery. Is
that true?
3. If I use stored procedure to replace the query, an error ocrured :
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection....
What is that? I've check ANSI_NULLS and ANSI_WARNINGS in both
server, but still get the error.

Please help me, i'm getting frustated now

Thanks

Wouldn't you want something like

DELETE D1
FROM dbReport..DeliveryNotes D1,
ServerA.db2.dbo.DeliveryNotes D2
WHERE D1.ReceivingID=D2.ReceivingID AND dtModified
BETWEEN ? AND ?

Also note the design time is fragile so you may like to utilise this

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Resant" <resant_v (AT) yahoo (DOT) com> wrote