Thursday, June 27, 2013

Skipping procedure

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