Microsoft SQL Server - Stage OpenQuery - Target environment

This article outlines a potential setup for target environments for Microsoft SQL Server Stage OpenQuery generator.

Please note that the installation and configuration of this target environment fall outside the scope of biGENIUS support.

We will not offer assistance within the provided example in this article.

A Microsoft SQL Server Stage OpenQuery target environment has numerous alternative configurations and installations.

Below is a potential target environment setup for a Microsoft SQL Server Stage OpenQuery generator.

Prerequisite environment

Please set up the Microsoft SQL Server environment before configuring additional pieces in the environment to use the Microsoft SQl Server Stage Files generator first.

Setup environment

To load data with OpenQuery, we must first create a Linked Server to access the data from SQL Server.

We will take an example with an Oracle source database:

Check the availability of the ORAOLEDB.Oracle provider in your SQL Server installation:

If the Oracle provider is not installed, you must first install the Oracle ODAC components available here.

Allow in-process statements for the Oracle provider by executing the following commands:

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Execute the following code to create your Linked Server:

EXEC master.dbo.sp_addlinkedserver 
@server = N'ORACLE_19_DOCKER'
,@srvproduct=N'Oracle' 
,@provider=N'OraOLEDB.Oracle' --Keep this value
,@datasrc=N'//localhost:1521/ORCLPDB1'
GO
EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'ORACLE_19_DOCKER',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'AWDEMO2019',
@rmtpassword=N'AWDEMO2019'
GO

Please adapt the @datasrc, @rmtuser, and @rmtpassword parameter values to your Oracle installation.

Check the correct configuration of your Linked Server by selecting some data with an OpenQuery, for example:

SELECT * FROM OPENQUERY(ORACLE_19_DOCKER, 'SELECT * FROM AWDEMO2019.CREDITCARD')

You're now ready to replace the placeholders in your generated artifacts, deploy these artifacts, and subsequently load the data based on the Generator you are using with the following possible load controls: