...
Warning |
---|
|
DISCLAIMER: Transferring of XYZ components, travel history, and node data should be done by someone that has knowledge of the system as well as SQL server. |
XYZ Data Transfer – Overview
...
- facility
- floor
- aisle_path
- segment
- travel_path
- location
- level
- node
- aisle_path_node
- NodeCache
- TravelPathHistory
- travel_path_node
The Move_Cache_sp is used to move just the cache tables from one database to another:
TravelPathHistoryDetail
Info |
---|
|
- The only XYZ-related data that is NOT moved is that which defines which nodes are interconnected. Since ConnectedNodes is NOT moved, the PopulateConnectedNodes_sp must be run after transferring XYZ data.
|
...
- Disable the scheduled task operations in both the Source and Destination environments.
- Log in to ProTrack Warehouse.
- Click System under the Configuration menu.
Click on the Scheduled Task sub-tab.
Fig.1 System Configuration
- Select “N” for ALL of the drop down boxes.
Select all of the check boxes.
Fig.2 System Configuration - Disable System
- Click to save the changes.
- Ensure that all processing in both environments is complete.
- Scheduled Request Records.
- Click on the Monitor tab.
- Click on the Scheduled Request tab.
- Click .
- Search for all records where the status is INP.
- If there are any records in INP wait for these records to leave INP status. All WMS processing is complete when there are no records in the Scheduled Request that are in INP status.
- Batch Process Records.
- XYZ data is imported based on a batch process that runs based on a configurable time interval.
- Check that there are no files in the import directories on the application server for the following import types:
- Facility
- Floor
- Aisles Path
- Segment
- Location
- Travel Path
- During the XYZ transfer process you will also want to ensure that nodes are not generated from the facility layout screen in either environment. This process cannot be disabled and will have to be independently monitored by the users.
- Access SQL for the database that is going to be updated. This database will be referred to as the Destination database. The database that contains the updated XYZ information will be referred to as the Source database.
- Ensure that the following stored procedures have been installed in the destination database:
- Move_Aisle_Path_Node_sp.sql
- Move_Aisle_Path_sp.sql
- Move_Cache_sp.sql
- Move_Facility_sp.sql
- Move_Floor_sp.sql
- Move_Level_sp.sql
- Move_Location_sp.sql
- Move_LocationData_sp.sql
- Move_Node_sp.sql
- Move_Segment_sp.sql
- Move_Travel_Path_Node_sp.sql
- Move_Travel_Path_sp.sql
- LinkedServerConnection_sp.sql
- Backup both the Source and Destination databases.
- It is assumed that when Move_LocationData_sp is being run, Move_Cache_sp will also be run afterward.
Temporarily disable constraints by running the following statement.
Code Block |
---|
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" |
- Delete all XYZ data in the destination database by opening a new query (connected to the destination database) and run the following statements, one-by-one from top to bottom.
Code Block |
---|
Delete from level
Delete from location
Delete from segment
Delete from aisle_path_node
Delete from travel_path_node
Delete from node
Delete from aisle_path
Delete from travel_path
Delete from TravelPathHistoryDetail
Delete from TravelPathHistory
Delete from NodeCache
|
- Open a new Query connected to the destination database.
Run the following query:
Code Block |
---|
exec dbo.Move_LocationData_sp
@SourceDB = 'Insert Source DB Name Here',
@DestDB = 'Insert Destination DB Name Here',
@SourceServer = 'Insert Source Server Name or IP address Here',
@DestServer = 'Insert Destination Server Name or IP address Here',
@LinkedUserName = 'protrack',
@LinkedUserPwd = 'protrack',
@Debug = 0
|
Wait for the query to result as complete.
Next, open a new Query connected to the destination database and run the stored procedure that will connect the nodes of a floor.
Code Block |
---|
exec PopulateConnectedNodes_sp |
Enable the constraints by running the following statement.
Code Block |
---|
EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" |
To move the travel path history, travel path details, and node cache:Delete all cache data in the destination database by opening a new query (pointed at the destination database) and run the following statements, one-by-one from top to bottom:
Code Block |
---|
Delete from TravelPathHistoryDetail
Delete from TravelPathHistory
Delete from NodeCache
|
- Open a new Query connected to the source database.
Run the following query:
Code Block |
DECLARE @return_value int
EXEC @return_value = [dbo].[Move_Cache_sp]
@SourceDB = N'Insert Source DB Name Here',
@DestDB = N'Insert Destination DB Name Here',
@SourceServer = NULL,
@DestServer = N'Insert Destination Server Name or IP address Here',
@Action = N'INSERT'
SELECT 'Return Value' = @return_value
GO
- Validate that all known differences between the two databases have been synchronized and that all of the effected affected tables have the same number of records.
- Run any additional comparisons as seen fit until confident that the data was transferred successfully
- If there are any discrepancies restore the databases from their backups and double check that steps 1 and 2 had been completed successfully.
- If problems still exist contact TZA.
- Enable the scheduled task operations in both the Source and Destination environments.
- Log in to ProTrack Warehouse.
- Click the System tab under the Configuration menu.
- Click on the Scheduled Task sub-tab.
- Select “Y” for all of the drop down boxes.
- Select all of the check boxes.
- Click to save the changes.