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