Try
Create procedure [dbo].[testETL]
as
begin
-- Carrier(power Real -> varchar(50))
set nocount on
-- create cursor for server list
DECLARE cControl cursor for select srvname from sys.sysservers where srvid!=0 order by 1 desc
DECLARE @CurrentServer varchar(50)
DECLARE @MSG varchar(100)
DECLARE @LoadFromDate varchar(20)
DECLARE @LoadToDate varchar(20)
OPEN cControl
if @CurrentServer in ('SSD-PBX')
begin
set @MSG = 'SSD-PBX' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
end
else
FETCH NEXT FROM cControl Into @CurrentServer
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
if @CurrentServer in ('SSD-PBX')
begin
set @MSG = 'SSD-PBX' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
end
else
set @msg = 'Start pulling data from ' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
-- base tables already exists, loading only
if @CurrentServer in ('SSD-PBX','nms-crawford-nss10-INF')
begin
set @MSG = 'SSD-PBX' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
end
else
exec usp_LoadConfigurationTablesFromNMS @CurrentServer
-- load nms SAT_DS status
-- determine the time range for nms_archive, min -> from date, max -> to date
if @CurrentServer in ('SSD-PBX','nms-crawford-nss10-NMS','nms-crawford-nss10-INF')
begin
set @MSG = 'SSD-PBX or nms-crawford-nss10-NMS' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
end
else
exec usp_DetermineTimeRangeForSAT_DS
@remoteNMS = @CurrentServer,
@FromTimeStamp = @LoadFromDate out,
@ToTimeStamp = @LoadToDate out
--start loading SAT_stats_DS
if @CurrentServer in ('SSD-PBX','nms-crawford-nss10-NMS','nms-crawford-nss10-INF')
begin
set @MSG = 'SSD-PBX or nms-crawford-nss10-NMS' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
end
else
if @LoadToDate != [dbo].[dateToTimestamp](dateadd(hh,1,[dbo].[TimeStampToDate](@LoadFromDate)))
begin
exec [dbo].[usp_LoadSATStatusTablesFromRemote_DS] @CurrentServer,@LoadFromDate,@LoadToDate
end -- end if
else
begin
set @MSG = 'Skipping nms_SAT_stats_DS due to same date. server is:' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
end
set @msg = 'Done pulling data from ' + @CurrentServer
exec [usp_PrintAndLogMSG] @MSG,'MainETL'
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
FETCH NEXT FROM cControl Into @CurrentServer
end
CLOSE cControl;
DEALLOCATE cControl;
end
Many Thanks & Best Regards, Hua Min
No comments:
Post a Comment