Automatizar Open Query en MS SQL Server

Esta tabla y conjunto de procedimientos almacenados te permitirán automatizar la transferencia de datos desde bases de datos Oracle y Progress a SQL Server.

Crear los módulos Get Open Query

Asumimos que tienes el conocimiento y el permiso para crear tablas, procedimientos almacenados, objetos Linked Server y trabajos, sin esto puede haber problemas con los que no podemos ayudarte.

Cuando ejecutamos esto, tendemos a colocarlo en una base de datos de tipo Utilidades donde guardamos funciones compartidas y objetos de mantenimiento.

La primera tarea que tenemos es crear una tabla maestra para gestionar todo el flujo de datos, algunas de las columnas a tener en cuenta incluyen:

GetOpenQueryStream - Este es un campo entero que permite procesar conjuntos de tablas por lotes, y se ejecutará en el orden establecido en la columna GetOpenQueryOrder.
GetOpenQueryName - Debería ser GetOpenQuery_Progress o GetOpenQuery_Oracle a menos que hayas adaptado el código.
GetOpenQueryLinkServ - Es el nombre del servidor enlazado, que debe configurarse en Objetos de Servidor.
GetOpenQuerySourceSchema - Es el esquema de la base de datos fuente.
GetOpenQueryDatabase, GetOpenQuerySchema, GetOpenQueryTable forman el identificador de tres partes donde se copian los datos.
Se pueden añadir declaraciones WHERE, INNER y TOP.
GetOpenQuerySkipCols - esto eliminará las columnas de la tabla al importar.

Crear tabla

CREATE TABLE dbo.GetOpenQuery(
GetOpenQueryID int IDENTITY(1,1) NOT NULL,GetOpenQueryStream int NULL,GetOpenQueryName nvarchar(100) NULL,GetOpenQueryLinkServ nvarchar(100) NULL,GetOpenQueryDatabase nvarchar(100) NULL,GetOpenQuerySchema nvarchar(max) NULL,GetOpenQueryTable nvarchar(100) NULL,GetOpenQueryWHERE nvarchar(1000) NULL,GetOpenQueryTOP nvarchar(100) NULL,GetOpenQuerySourceSchema nvarchar(100) NULL,GetOpenQuerySkipCols nvarchar(max) NULL,GetOpenQueryINNER nvarchar(max) NULL,GetOpenQuerySkipTruncate bit NULL DEFAULT ((0)),GetOpenQueryOrder bit NULL DEFAULT ((0)))

Un hub

Los trabajos pueden conectarse para pasar un ID de flujo y realizar un bucle a través de las tablas relevantes mediante este procedimiento almacenado. Esto hace un bucle a través de la tabla de arriba, y dispara el código relevante para el servidor vinculado Oracle o Progress.

Esto se convierte entonces en el centro por el cual todos los otros trabajos se pueden llamar.

Crear procedimiento almacenado

CREATE PROC [dbo].[GetOpenQuery_Data](@Stream INT) AS BEGINDECLARE @GetOpenQueryName NVARCHAR(100),@GetOpenQueryLinkServ NVARCHAR(100),@GetOpenQueryDatabase NVARCHAR(100),@GetOpenQuerySchema NVARCHAR(MAX),@GetOpenQueryTable NVARCHAR(100),@GetOpenQueryWHERE NVARCHAR(1000),@GetOpenQueryTOP NVARCHAR(100),@SrcScheme NVARCHAR(100),@SkipCols NVARCHAR(MAX),@GetOpenQueryINNER NVARCHAR(MAX),@GetOpenQuerySkipTruncate BIT DECLARE @SQL NVARCHAR(MAX),@SQLRows BIGINT,@SQLRowCount BIGINT,@SQLOutPut NVARCHAR(100)='@SQLRows BIGINT' DECLARE C CURSOR FAST_FORWARD FORSELECTGetOpenQueryName, GetOpenQueryLinkServ, GetOpenQueryDatabase, GetOpenQuerySchema, GetOpenQueryTable,GetOpenQueryWHERE, GetOpenQueryTOP, GetOpenQuerySourceSchema, GetOpenQuerySkipCols, GetOpenQueryINNER, GetOpenQuerySkipTruncateFROM GetOpenQueryWHERE GetOpenQueryStream=@StreamORDER BY GetOpenQueryOrderOPEN CFETCH NEXT FROM CINTO @GetOpenQueryName,@GetOpenQueryLinkServ,@GetOpenQueryDatabase,@GetOpenQuerySchema,@GetOpenQueryTable,@GetOpenQueryWHERE,@GetOpenQueryTOP,@SrcScheme,@SkipCols,@GetOpenQueryINNER,@GetOpenQuerySkipTruncateWHILE @@FETCH_STATUS = 0 BEGIN       BEGIN TRY       SET @SQL='EXEC '+@GetOpenQueryName+' '''+       @GetOpenQueryLinkServ+''','''+       @GetOpenQueryDatabase+''','''+       @GetOpenQuerySchema+''','''+       @GetOpenQueryTable+''','+       ISNULL(NULLIF(''''+@GetOpenQueryWHERE+'''',''),'NULL')+','+       ISNULL(NULLIF(@GetOpenQueryTOP,''),'NULL')+','+       ISNULL(NULLIF(@SrcScheme,''),'NULL')+','+       ISNULL(NULLIF(''''+@SkipCols+'''',''),'NULL')+','+       ISNULL(NULLIF(''''+@GetOpenQueryINNER+'''',''),'NULL')+','+(CASE WHEN @GetOpenQuerySkipTruncate=1 THEN '1' ELSE '0' END)       EXEC sp_executesql @SQL       SET @SQLRows=@@ROWCOUNT       SELECT @SQLRows       END TRY       BEGIN CATCH        PRINT @GetOpenQueryLinkServ+'; Table '+@GetOpenQueryTable+' Failed'       END CATCH       PRINT @SQL       FETCH NEXT FROM C       INTO @GetOpenQueryName,@GetOpenQueryLinkServ,@GetOpenQueryDatabase,@GetOpenQuerySchema,@GetOpenQueryTable,       @GetOpenQueryWHERE,@GetOpenQueryTOP,@SrcScheme,@SkipCols,@GetOpenQueryINNER,@GetOpenQuerySkipTruncateEND CLOSE C;DEALLOCATE C;ENDGO

Cómo llamar a los trabajos

Ahora que ya han sido creados estos pasos, podemos plomear en los SPROC's restantes para manejar tanto Oracle como Progress.

Para lanzar el Flujo 1, crea un Trabajo de Agente SQL con el siguiente código:

EXEC Utilities.dbo.GetOpenQuery_Data 1