Obtener Open Query - Progress
Este procedimiento almacenado dinámico importará datos de una base de datos Progress vinculada a SQL Server mediante OpenQuery.
Acerca de
El módulo Oracle permite importar datos de una base de datos OpenEdge vinculada a SQL Server, ya sea de forma aislada o como parte del módulo creado en el documento principal, mediante el uso de SQL dinámico para generar las secuencias de comandos pertinentes.
Crear procedimiento almacenado
CREATE PROC [dbo].[GetOpenQuery_Progress](@LinkServ NVARCHAR(100),@Database NVARCHAR(100),@Schema NVARCHAR(100),@Table NVARCHAR(100),@WHERE NVARCHAR(1000),@TOP INT,@SrcScheme NVARCHAR(100)='PUB',@SkipCols NVARCHAR(MAX)='',@INNER NVARCHAR(MAX)='',@SkipTruncate BIT=0) AS BEGINSET NOCOUNT OFF;SET @SkipCols=ISNULL(@SkipCols,'')SET @SrcScheme=ISNULL(@SrcScheme,'PUB')DECLARE @OUPUT NVARCHAR(MAX)='@CREATE NVARCHAR(MAX) OUTPUT,@PROGRE NVARCHAR(MAX) OUTPUT'DECLARE @CREATE NVARCHAR(MAX),@PROGRE NVARCHAR(MAX),@SQL NVARCHAR(MAX)='SELECT @CREATE=COALESCE(@CREATE+'','','''')+QUOTENAME(COL)+'' ''+SQLType,@PROGRE=COALESCE(@PROGRE+'','','''')+(CASEWHEN COLTYPE=''character'' OR (COLTYPE=''varchar'' AND WIDTH>4000)THEN ''CAST(SUBSTRING("''+COL+''",1,4000) AS varchar(4000)) "''+COL+''" ''WHEN COLTYPE=''varchar''THEN ''CAST(SUBSTRING("''+COL+''",1,''+CONVERT(NVARCHAR(10),WIDTH)+'') AS varchar(''+CONVERT(NVARCHAR(10),WIDTH)+'')) "''+COL+''" ''WHEN COLTYPE=''date'' THEN ''CAST("''+COL+''" AS varchar(20)) "''+COL+''" ''--WHEN COLTYPE=''numeric'' AND WIDTH>''38'' THEN ''CAST("''+COL+''" AS varchar(60)) "''+COL+''" ''WHEN COLTYPE=''numeric'' THEN ''CAST("''+COL+''" AS FLOAT) "''+COL+''" ''ELSE ''"''+COL+''"'' END)FROM (SELECT ID,COL,COLTYPE,WIDTH,SCALE,(CASEWHEN COLTYPE=''varchar'' THEN ''NVARCHAR(MAX)''--WHEN COLTYPE=''numeric'' THEN ''DECIMAL(''+CONVERT(NVARCHAR(10),WIDTH)+'',''+CONVERT(NVARCHAR(10),SCALE)+'')''WHEN COLTYPE=''numeric'' THEN ''FLOAT''WHEN COLTYPE=''integer'' THEN ''INT''WHEN COLTYPE=''bigint'' THEN ''bigint''WHEN COLTYPE=''float'' THEN ''float''WHEN COLTYPE=''REAL'' THEN ''REAL''WHEN COLTYPE=''smallint'' THEN ''smallint''WHEN COLTYPE=''bit'' THEN ''bit''WHEN COLTYPE=''varbinary'' THEN ''varbinary(MAX)''WHEN COLTYPE=''character'' THEN ''NVARCHAR(MAX)''WHEN COLTYPE=''date'' THEN ''DATETIME2''WHEN COLTYPE=''time'' THEN ''time''WHEN COLTYPE=''timestamp'' THEN ''DATETIME2''WHEN COLTYPE=''timestamp_timezone'' THEN ''timestamp_timezone''ELSE NULL END) SQLTypeFROM OpenQuery('+@LinkServ+', ''select ID,COL,COLTYPE,WIDTH,SCALE from sysprogress.SYSCOLUMNS_FULLwhere OWNER = '''''+@SrcScheme+''''' AND TBL = '''''+@Table+'''''AND LEFT(COL,5)<>''''SPARE''''ORDER BY ID'')) xWHERE '','+@SkipCols+','' NOT LIKE ''%,''+COL+'',%'''--PRINT @SQLEXEC sp_executesql @SQL,@OUPUT,@CREATE OUTPUT,@PROGRE OUTPUT--PRINT @CREATE--PRINT @PROGREDECLARE @DROPTABLE NVARCHAR(MAX)='IF NOT OBJECT_ID(''['+@Database+'].['+@Schema+'].['+@Table+']'') IS NULL DROP TABLE ['+@Database+'].['+@Schema+'].['+@Table+']'DECLARE @CREATETABLE NVARCHAR(MAX)='CREATE TABLE ['+@Database+'].['+@Schema+'].['+@Table+']('+@CREATE+')'DECLARE @SELECTTOP NVARCHAR(100)=''IF @WHERE IS NOT NULL SET @WHERE=' '+@WHERE+' 'IF @WHERE IS NULL SET @WHERE=''IF @INNER IS NOT NULL SET @INNER=' '+@INNER+' 'IF @INNER IS NULL SET @INNER=''IF ISNULL(@TOP,0)<>0 SET @SELECTTOP=' TOP '+CONVERT(NVARCHAR(10),@TOP)DECLARE @SELECTRowCount NVARCHAR(MAX)='@SQLRowCount BIGINT OUTPUT'DECLARE @SELECT NVARCHAR(MAX)='INSERT INTO ['+@Database+'].['+@Schema+'].['+@Table+']SELECT *FROM OpenQuery('+@LinkServ+', ''SELECT '+@SELECTTOP+' '+@PROGRE+'FROM '+@SrcScheme+'."'+@Table+'"'+@INNER+''+@WHERE+' WITH (NOLOCK) '')'PRINT @SELECTIF @SkipTruncate=0 EXEC sp_executesql @DROPTABLEIF @SkipTruncate=0 EXEC sp_executesql @CREATETABLEEXEC sp_executesql @SELECTENDEs posible que tengas que personalizarlo ligeramente, pero debería darte una base para ejecutar código fácilmente.
