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.[uspMasterDoSynchDataFromRaw]
--not so sure about the cleaning for this one--
--@canCleanStagingTables bit
AS
BEGIN
DECLARE @batchDateTable TABLE
(BatchLogID bigint,
TableName varchar (50),
BatchDate datetime2(7)
)
DECLARE @batchLogID BIGINT,
@resultOk int,
@canProcess bit;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Initialize certain processing variables
SET @canProcess = 0;
BEGIN TRY
--Get BatchDate to process (from BatchLogs)
INSERT INTO @batchDateTable (BatchLogID,TableName, BatchDate)
SELECT BatchLogID, TableName, BatchDate
FROM dbo.ufnGetBatchLogBatchDate('EXTCOMPLETE', null)
ORDER BY BatchDate;
--Check to see if there is actually any data in the raws to load for these periods
--the data may have been removed from raws after the batch log was told ok, set to EXTCOMPLETE
--Check zraw._conConsult
IF (SELECT COUNT(1) FROM zraw._conConsult WHERE BatchLogID IN(SELECT BatchLogID FROM @batchDateTable)) > 0
BEGIN
SET @canProcess = 1;
END
IF @canProcess = 1
--If there are any periods to do stuff to, process away!
--Because of problems with log file growing to enormous size, a shrink log is done after every sync step.
BEGIN
--Set all BatchLogs LoadStatus fields = 'INTPROCESSING'
UPDATE dbo.BatchLogs SET LoadStatus = 'INTPROCESSING'
WHERE BatchLogID In(SELECT BatchLogID FROM @batchDateTable)
/*****SYNC DEFINITIONS***/
--synch Status types
EXEC zraw.uspDoSynchStatusTypes
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS --wont be able to do this @CDW
--synch Request Services
EXEC zraw.uspDoSynchRequestServices
--sync Stations
--EXEC uspDoSynchStations
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
/****FILL IN FK HOOKS PRIOR TO CORE SYNC****/
-- fill visn hooks
--EXEC uspDoSynchRawFKVisns
--DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
--HOWEVER, only do this if the incoming parameter @canCleanStagingTables = true (1)
--SELECT @batchLogID = MIN(BatchLogID)
--FROM @batchDateTable
--WHILE @batchLogID <> 0
-- BEGIN
-- --update synch logs for how many fact records inserted/updated
-- EXEC uspSetSynchLogs @batchLogID
-- IF @canCleanStagingTables = 1
-- BEGIN
-- --delete the raws
-- EXEC uspDoDeleteRaw @batchLogID, @resultOk OUTPUT
-- DBCC SHRINKFILE (N'LogData' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
-- END
-- --get the next key in the @batchDateTable
-- --should only be one batch per day, but just in case there's more--
-- SELECT @batchLogID = MIN(BatchLogID)
-- FROM @batchDateTable
-- WHERE BatchLogID > @batchLogID
--END
--Next, set all BatchLogs LoadStatus fields = 'INTCOMPLETE' where matches the
--@batchDateTable choices, to show the load was completed
UPDATE dbo.BatchLogs SET LoadStatus = 'INTCOMPLETE', LoadCompleteDate = GETDATE()
WHERE BatchLogID In(SELECT BatchLogID FROM @batchDateTable)
--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
END TRY
BEGIN CATCH
PRINT 'There was an error with the master batch synchronizer' + CHAR(13);
THROW;
END CATCH
END