Prev: Check Constraint and function call
Next: Trigger error
From: Jordan on 16 Jun 2010 18:18
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?





From: Eric Isaacs on 16 Jun 2010 20:00
Yes it is possible. Create a LINKED SERVER from SQL Server to the
Pervasive database. Synonyms are helpful for this if you're using SQL
Server 2005+, so that the remote table can be referenced in one place,
rather than throughout the code. You're using 2000, so you'll need to
hard code the table name in the sproc. Since it looks like you only
need read-only access to the Pervasive data, you might consider
creating a view of that Pervasive table instead of a synonym, also
provided that you're reusing it in multiple places.

Create a sproc that pulls the data in from the remote table. I would
suggest you bring it into TempDB first in a temporary table (to
minimize locking in both databases.) After it's in TempDB, issue an
update statement from the data in tempdb to the table in SQL Server.

Create a job and schedule it to run each night when you want it to run
that calls that procedure.

-Eric Isaacs
J Street Technology, Inc.
www.jstreettech.com
 | 
Pages: 1
Prev: Check Constraint and function call
Next: Trigger error