Web design and hosting, database, cloud and social media solutions that deliver business results
  • Business Solutions
    • Robotic Process Automation
    • Software
    • Database Consultancy Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
      • Server Upgrade and DBA Services
    • Web Site Design Services
      • Logo Design
      • Payment Gateways
      • Web Localisation and Translation
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Management and Advice Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About Us
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Careers
      • Translator English-Portuguese
      • Translator English-Spanish
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Deepika Bandaru
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
Deutsch (DE)English (EN-US)English (EN-GB)Español (ES)Français (FR)हिंदी (HI)italiano (IT)日本語 (JA)

Automate Open Query in MS SQL Server

This Table and set of Stored Procedures will enable you to automate the transfer of data from Oracle and Progress databases into SQL Server.

Creating The Get Open Query Modules

We are assuming that you have the knowledge and permission to create tables, stored procedures, Linked Server objects and jobs, without this then there may be issues that we cannot help you with.

When running this, we tend to place it in a Utilities type database where we keep shared functions and maintenance objects.

First task we have is to create a master table to manage all of the data flow, a few of the Columns to keep an eye out for include;

  • GetOpenQueryStream - This is an integer field that allows you to batch process sets of tables, and will run in the order set in the column GetOpenQueryOrder 
  • GetOpenQueryName - This should be either GetOpenQuery_Progress or GetOpenQuery_Oracle unless you have adapted the code.
  • GetOpenQueryLinkServ - Is the linked server name, which needs to be set up in Server Objects
  • GetOpenQuerySourceSchema - Is the source database schema
  • GetOpenQueryDatabase, GetOpenQuerySchema, GetOpenQueryTable form the three part identifier for where the data is set to be copied to.
  • You can add in WHERE, INNER and TOP statements
  • GetOpenQuerySkipCols - this will remove the columns from the table when importing.

Create Table

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)))

A hub

Jobs can then be hooked to pass in a Stream ID and loop through the relevant tables via this stored Procedure. This loops through the table above, and fires off the relevant code for either Oracle or Progress linked server.

This then becomes the hub by which all other jobs can be called.

Create Stored Procedure

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

How to call the jobs

Now that these steps have been created, we can plumb in the remaining SPROC's to handle both Oracle and Progress.

To fire off Stream 1, create an SQL Agent Job with the following code;

EXEC Utilities.dbo.GetOpenQuery_Data 1

Get Available Columns

This set of Stored Procedures will enable you to view the available columns in a table for Oracle and Progress databases linked to SQL Server.

Get Available Tables

This set of Stored Procedures will enable you to view the available tables for Oracle and Progress databases linked to SQL Server.

Oracle SQL

This dynamic Stored Procedure will import data from an Oracle Database linked into SQL Server using OpenQuery

Progress SQL

This dynamic Stored Procedure will import data from an Progress Database linked into SQL Server using OpenQuery

Author

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2020

Registered in England and Wales 08985867

Site Links

RSSLoginLink Cookie PolicySitemap

Social Media

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

Get in Touch

+15125961417info@claytabase.comClaytabase USA, 501 Congress Avenue, Suite 150, Austin, Texas, 78701, United States

Partnered With

The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Logout
Ousia CMS Loader