November 1, 2007 at 10:01 am
Hi experts,
I am very new at SQL and although the books I ordered to learn from are still in the mail... I volunteered to try to schedule some synchronization between Pervasive tables and SQL Server tables. Please help. I have created one DTS package and it executes but not correctly (it just adds records creating duplicates).
OUTLINE: I have the same eight tables in four databases, these databases are in Pervasive. I have created those eight tables in one SQL Server 2000 database. Each new table got one new field in SQL Server to identify which of the four Pervasive databases the data came from. I have filled the new tables in SQL Server with the data from Pervasive. I would now like to keep the new SQL Server tables synchronized with the Pervasive tables.
--Some tables when combined in SQL Server have over one million records
--Some tables when combined in SQL Server are small enough to delete and recreate
--Some tables add records only
--Some tables add and modify records
--No table deletes
--No table has a last modified date field
--All tables require two or more fields to make a unique record
--All the databases are closed at year end and new ones started. This results in some tables going to zero records and some stay the same (e.g. the million record table is one that goes to zero) so even the largest table won't get much past 1.5 million records.
QUESTION: Could someone please get me started with example SQL to do this synchronization and any incidental dabase concepts as to why it is done one way verses another. Links to examples or discussions are appreciated as well.
Thanks very much.
Hope
November 1, 2007 at 1:51 pm
Continuing,
While waiting for comments some simple things done already:
1. created a SQL Server Agent Job with steps that executes when required
2. step one in the job is drop table - it works
3. step two in the job is create table - it works
4. step three in the job is create index - it works
5. steps 4, 5, 6, and 7 are for filling the table from the four Pervasive db's - don't work
I think steps 4, 5, 6, and 7 need to be stored procedures. This is looking easier. Still, any comments are welcome I'm just winging-it right now.
Hope
November 1, 2007 at 2:23 pm
Still working...
When the stored procedure syntax check as good but I get the error
Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. ...
Where do I set them?? I put them right after AS in the sproc but that was not accepted. Where do I set them??
November 2, 2007 at 12:44 pm
Continuing...
Things work. When I executed the CREATE PROC from Query Analyser everything was fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply