Revisión de sincronización de cajas

DECLARE @LocalTable TABLE(
ServerName VARCHAR(MAX)
, EXECUTION_TIME DATETIME
, SyncType VARCHAR(20)
)

DECLARE @name nvarchar(max)
, @query nvarchar(max)

SELECT *
INTO #tempPos
FROM Sys.servers ss
WHERE NOT ss.server_id in (0)
AND ss.name LIKE '%POS%'

SELECT * INTO #SharedTable FROM @LocalTable

SELECT ServerName, CAST('' AS VARCHAR(MAX)) AS [STATUS] INTO #SharedTable2 FROM @LocalTable

WHILE EXISTS (SELECT 1 FROM #tempPos)
BEGIN

SELECT TOP 1 @name = name
FROM #tempPos

--/Consutlta Count Skus/ SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server,Count() FROM ' + QUOTENAME(@name) + '.[POSDB].[pos].Itemplu' --/Consulta FULLs Dispares / SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server,Sum(Case When [Type] = '+ '''Full_END'''+' Then 1 else 0 end) '+ '''Full_END'''+',Sum(Case When [Type] = '+ '''Full_Begin'''+' Then 1 else 0 end) '+ '''Full_Begin'''+ ' FROM ' + QUOTENAME(@name) + '.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) and not TYPE = ' + '''DELTA'''+'Having Sum(Case When [Type] = '+ '''Full_END'''+' Then 1 else 0 end) != Sum(Case When [Type] = '+ '''Full_Begin'''+' Then 1 else 0 end) OR (( Select Count() FROM ' + QUOTENAME(@name) +'.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) and [Type] = '+ '''Full_Begin'''+ ')=0)'
/Consulta Detalle Fulls DC/ SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server, * FROM ' + QUOTENAME(@name) + '.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) and not TYPE = ' + '''DELTA'''+' ORder by EXECUTION_TIME desc'
--/Detalle Full/Delta DC/ SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server, * FROM ' + QUOTENAME(@name) + '.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) ORder by EXECUTION_TIME desc'

BEGIN TRY
    --PRINT @query
    EXEC sp_executesql @query
    INSERT INTO #SharedTable(ServerName, EXECUTION_TIME, SyncType) VALUES(@name,GETDATE(),'1')
END TRY
BEGIN CATCH
    INSERT INTO #SharedTable2 (ServerName, [STATUS]) VALUES ( @name,ERROR_MESSAGE())
END CATCH

DELETE FROM #tempPos where name = @name

END

SELECT * FROM #SharedTable2

DROP TABLE #tempPos
DROP TABLE #SharedTable
DROP TABLE #SharedTable2

/### BORRAR Tabla FileSyncExecution para Forzar FULL SYNC # NOTA Solo usarlo en cajas con falta de Informacion/

---- Delete [8638-pos24].POSDB.dbo.FileSyncExecution

DECLARE @LocalTable TABLE(
ServerName VARCHAR(MAX)
, EXECUTION_TIME DATETIME
, SyncType VARCHAR(20)
)

DECLARE @name nvarchar(max)
, @query nvarchar(max)

SELECT *
INTO #tempPos
FROM Sys.servers ss
WHERE NOT ss.server_id in (0)
AND ss.name LIKE '%POS%'
SELECT * INTO #SharedTable FROM @LocalTable
SELECT ServerName, CAST('' AS VARCHAR(MAX)) AS [STATUS] INTO #SharedTable2 FROM @LocalTable
WHILE EXISTS (SELECT 1 FROM #tempPos)
BEGIN

SELECT TOP 1 @name = name
FROM #tempPos

/*Consulta FULLs Dispares */ SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server,Sum(Case When [Type] = '+ '''Full_END'''+' Then 1 else 0 end) '+ '''Full_END'''+',Sum(Case When [Type] = '+ '''Full_Begin'''+' Then 1 else 0 end) '+ '''Full_Begin'''+ ' FROM ' + QUOTENAME(@name) + '.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) and not TYPE = ' + '''DELTA'''+'Having Sum(Case When [Type] = '+ '''Full_END'''+' Then 1 else 0 end) != Sum(Case When [Type] = '+ '''Full_Begin'''+' Then 1 else 0 end) OR (( Select Count() FROM ' + QUOTENAME(@name) +'.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) and [Type] = '+ '''Full_Begin'''+ ')=0)' --/Consulta Detalle Fulls DC*/ SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server, * FROM ' + QUOTENAME(@name) + '.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) and not TYPE = ' + '''DELTA'''+' ORder by EXECUTION_TIME desc'
--/Detalle Full/Delta DC/ SET @query = 'SELECT ''' + QUOTENAME(@name) + ''' AS Server, * FROM ' + QUOTENAME(@name) + '.[POSDB].[dbo].FileSyncExecution WHERE EXECUTION_TIME >= convert(date,getdate()) ORder by EXECUTION_TIME desc'

BEGIN TRY
--PRINT @query
EXEC sp_executesql @query
INSERT INTO #SharedTable(ServerName, EXECUTION_TIME, SyncType) VALUES(@name,GETDATE(),'1')
END TRY
BEGIN CATCH
INSERT INTO #SharedTable2 (ServerName, [STATUS]) VALUES ( @name,ERROR_MESSAGE())
END CATCH
DELETE FROM #tempPos where name = @name
END
SELECT * FROM #SharedTable2
DROP TABLE #tempPos
DROP TABLE #SharedTable
DROP TABLE #SharedTable2

/### BORRAR Tabla FileSyncExecution para Forzar FULL SYNC # NOTA Solo usarlo en cajas con falta de Informacion/

--- Delete [8747-pos04].POSDB.dbo.FileSyncExecution