Jordan
2010-05-25 18:54:24 UTC
We have a MS SQL 2000 server and an Pervasive 2000i SP4 Server on seperate
boxes. There is a table on the Pervasive box that has a list of parts with
descriptions and other information that I need to have partially syncronized
wtih my MS box.
Right now I have an MS Access front end connecting to both tables via ODBC
that has an append query and an update query to add and update just the
information I need to the MS SQL server:
1. Get all the new Part Numbers, Descriptions, and Price out of [Part
Master] on the Pervasive box and append them to the table on the MSSQL box
if the part does not already:
INSERT INTO tblParts ( PartID, Description1, Description2, Cost )
SELECT [Part Master].PRTNUM_01,
[Part Master].PMDES1_01,
[Part Master].PMDES2_01,
[Part Master].COST_01
FROM [Part Master] LEFT JOIN Parts ON [Part Master].PRTNUM_01 = Parts.PartID
WHERE (((Parts.PartID) Is Null));
2. Update all the Part Descriptions on the MSSQL box with all the current
descriptions from the Pervasive box.
UPDATE [Part Master] INNER JOIN Parts
ON [Part Master].PRTNUM_01 = Parts.PartID
SET Parts.Discription1 = [PMDES1_01],
Parts.Description2 = [PMDES2_01],
Parts.Cost = [COST_01],
Parts.PlannerID = [PLANID_01];
The MS SQL server has the Pervasive client installed and ODBC Connection
setup. Is it possible for me to have the MSSQL server run the two updates
itself at night rather than have to use the Access queries?
boxes. There is a table on the Pervasive box that has a list of parts with
descriptions and other information that I need to have partially syncronized
wtih my MS box.
Right now I have an MS Access front end connecting to both tables via ODBC
that has an append query and an update query to add and update just the
information I need to the MS SQL server:
1. Get all the new Part Numbers, Descriptions, and Price out of [Part
Master] on the Pervasive box and append them to the table on the MSSQL box
if the part does not already:
INSERT INTO tblParts ( PartID, Description1, Description2, Cost )
SELECT [Part Master].PRTNUM_01,
[Part Master].PMDES1_01,
[Part Master].PMDES2_01,
[Part Master].COST_01
FROM [Part Master] LEFT JOIN Parts ON [Part Master].PRTNUM_01 = Parts.PartID
WHERE (((Parts.PartID) Is Null));
2. Update all the Part Descriptions on the MSSQL box with all the current
descriptions from the Pervasive box.
UPDATE [Part Master] INNER JOIN Parts
ON [Part Master].PRTNUM_01 = Parts.PartID
SET Parts.Discription1 = [PMDES1_01],
Parts.Description2 = [PMDES2_01],
Parts.Cost = [COST_01],
Parts.PlannerID = [PLANID_01];
The MS SQL server has the Pervasive client installed and ODBC Connection
setup. Is it possible for me to have the MSSQL server run the two updates
itself at night rather than have to use the Access queries?