Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?-- =========================================
-- Description: Master raw synchronizer proc, synching data into ODS from incoming data found in the incoming raw table(s)
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE zraw.uspMasterDoSyncDataFromRawConsultDomain
--not so sure about the cleaning for this one--
--@canCleanStagingTables bit
AS
BEGIN
/*
Steps for processing a data domain
1. Because there is more than one table to contend with, each script below will have to run
2. Inside each table-specific synchronization script, the following things will occur:
--determine what batches are available for processing
--update the BatchLogs table, setting those specific table's EXTCOMPLETE batches to INTPROCESSING
--clean the raw table's processing batch records
--perform any backfill of foreign keys into the table in question
--complete sync of raw records that are ok to process by loading into normalized tables
--delete processed records from raw
--set records in BatchLogs to INTCOMPLETE
--record in SyncLogs how many records were affected
3. "domain" related master procs will only have EXEC commands in a try/catch.
*/
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
/*****SYNC DEFINITIONS***/
--sync Stations
EXEC zraw.uspDoSyncStations;
--synch Status types
EXEC zraw.uspDoSyncStatusTypes;
--synch Request Services
EXEC zraw.uspDoSyncRequestServices;
--synch Request Services
EXEC zraw.uspDoSyncStopCodes;
--synch Request Services
EXEC zraw.uspDoSyncAssociatedStopCodes;
/****FILL IN FK HOOKS PRIOR TO CORE SYNC****/
-- fill visn hooks
--EXEC uspDoSynchRawFKVisns
--TODO: finish adding definition table synchronizers
--TODO: add core table synchronizers
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
--shrink staging data
--DBCC SHRINKFILE (N'StagingData1' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
--final log shrink
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
--EXEC uspMasterDoShrinkReindexAll; --moving this out of the SQL proj into a seperate job task. too many issues with ETL account permissions--
END TRY
BEGIN CATCH
PRINT 'There was an error with the master consult-related batch synchronizer' + CHAR(13);
THROW;
END CATCH
END