Web design and hosting, database, cloud and social media solutions that deliver business results
  • Geschäftslösungen
    • Robotische Prozessautomatisierung
    • Software
    • Datenbank-Beratungsdienste
      • Datawarehouse-Dienste
      • Datenintegration
      • PowerBI
      • Werkzeuge
    • Website Design
      • Weblokalisierung und -übersetzung
      • Website Sicherheit
    • Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Sozialen Medien
  • Akademie
    • Unsere Testumgebung
    • Datenbankdesign lernen
      • SQL Server 2008 Wartungsplan
      • Offene Abfrage abrufen
      • Verwenden von SQL Server Daten
      • Verwenden von SQL Server Datum
      • Verwenden von SQL Server Funktionen
      • Verwenden von SQL Server Pivot-Unpivot
    • Lern Web Design
      • ASP-NET
      • CSS
      • Verwendung von JavaScript
    • Lernen von IT-Diensten
      • Anfordern von SSL und Generierung von PFX-Datei in OpenSSL Simple Steps
  • Über
    • Bloggen
    • Karriere
    • Mannschaft
      • Adrian Ananda
      • Ali Al-Amine
      • Ayse Hur
      • Chester Copperpot
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
    • Mappe
Deutsch (DE)English (EN-GB)English (EN-US)Español (ES)हिंदी (HI)italiano (IT)日本語 (JA)

Abfrage öffnen – Fortschritt

Diese dynamische gespeicherte Prozedur importiert Daten aus einer Fortschrittsdatenbank, die über OpenQuery mit SQL Server verknüpft ist

About

The Oracle module will enable you to import from data from an OpenEdge Database linked into SQL Server, either as a one off or as part of the module created in the main document by using dynamic SQL to generate the relevant scripts.

Create Stored Procedure

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 @SELECTEND
You may need to customise this slightly, but it should give you a basis for running code easily.

Was this helpful?

Please note, this commenting system is still in final testing.

Author

Copyright Claytabase Ltd 2020

Registered in England and Wales 08985867

RSSLoginLink Cookie-RichtlinieSeitenverzeichnis

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.comClaytabase Ltd, Unit 3d, Rink Road Industrial Estate, PO33 2LT, United Kingdom
Die Einstellungen auf dieser Seite sind so eingestellt, dass alle Cookies zulässig sind. Diese können auf unserer Cookie Policy & Settings Seite geändert werden. Wenn Sie diese Seite weiter nutzen, stimmen Sie der Verwendung von Cookies zu.
Ousia Logo
Logout
Ousia CMS Loader