Access CDS Objects Exposed as SQL Services Using M...

Frank-Martin · ‎03-02-2023

In my introductory blog post related to ABAP SQL services (Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs), I described how to expose CDS objects in a SQL Service, how to install the ODBC driver for ABAP on Windows, and how to use it in Excel. In this blog, we want to show you how to use the ODBC driver for ABAP in conjunction with Microsoft SQL Server.

SQL Server is a well know relational database that allows to create local database objects and to access them via SQL. In addition, like other major RDMS products it supports a federated feature that allows to access remote database objects defined in other databases. Similar database federation features are, for example, SDA in SAP HANA or Db2 Federation in IBM Db2. In SQL Server, such configured remote databases are called “Linked Servers” (“Remote Sources” in HANA SDA, “Federated Data Sources” in IBM Db2).

Prerequisites

Recently, the ODBC driver for ABAP was improved and enhanced to enable it for use with SQL Server. Therefore, please use at least ODBC DRIVER FOR ABAP 1.0 - Patch Collection 5.

If you haven’t defined a SQL Service in your ABAP back end yet and haven’t installed the ODBC driver for ABAP on your Windows host that contains the SQL Server Instance, follow the blog Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs). Also add a DSN (in the following example called “ALX” on our SQL Server test host).

DSN configuration

Defining a Linked Server in SQL Server

SQL Server allows to define connections to remote databases and calls them “linked servers”. You can use a linked server to access data from different data sources outside the SQL Server instance. SQL Server uses the OLE DB database interface to connect to the remote databases. As a default, several OLE DB providers for Microsoft products are available. Other vendor-specific OLE DB providers can be plugged in (for example, for IBM Db2 and SAP HANA).

Now let me show you how we defined a linked server in SQL server in our test scenario:

For the ABAP database, you can use the generic "Microsoft OLE DB provider for ODBC drivers". This OLE DB provider provides a bridge between the OLE DB and the ODBC interface.

General Linked Server Properties

You can now define a linked server for the “ODBC driver for ABAP”. In the General section, just enter the prepared DSN (“ALX” in our example):

Linked Server Security Properties

In the Security section, you choose the option "Be made with this security context" and enter your ABAP user name or password. Note that the ABAP client is defined in the DSN properties. The DSN also determines if the remote login name is an ABAP user or the alias name of an ABAP user.

You don’t need to change anything else in the Server Options section.

Running a Query on the ABAP Linked Server

Once the linked server has been defined, you can open a new query in the SQL Server Management Studio (SSMS) and run a simple query to retrieve data from the ABAP server. In our example, we query the content of the SYS.VIEWS table.

You need to use the OPENQUERY syntax to execute the pass-through query directly on the target. The remote ABAP server will compile the query, execute it, and return the result. SQL Server retrieves the result set, and it can join it with other local or remote data. For more information, check OPENQUERY (Transact-SQL) - SQL Server | Microsoft Learn.

Query on Linked Server

This is just an example query. For more information about the SQL dialect on ABAP SQL services, please see my blog SQL Queries on CDS objects Exposed as SQL Service | SAP Blogs .

Execution Plan

As may be expected, the execution plan in SSMS looks pretty simple. The query is executed completely on the target ABAP server.

Joining ABAP Data with Local SQL Server Data

Using the OPENQUERY syntax, you can build a query that joins remote data (obtained from the ABAP server) with local data, like, for example, the output of the system view sys.databases. Please note that this is just an example of a cross join, without any real relation between these two sets of data.

SELECT *

FROM OPENQUERY(ALX, 'SELECT * FROM SYS.VIEWS') 

cross join sys.databases

Such queries can also be encapsulated in a view definition:

use testdb

go



create view joinABAP

as



SELECT *

FROM OPENQUERY(ALX, 'SELECT * FROM SYS.VIEWS') cross join sys.databases

GO

A SELECT on the view will then return the desired result set:

select top 10 * from dbo.joinABAP

where database_id = 5

go

Using such federated queries, you can enrich data from a remote ABAP database with local data from SQL Server or from other linked servers.

Import Data from ABAP to SQL Server

In some cases, you might just want to extract data from an ABAP system and import it into a local SQL Server table, into a flat file, or other targets. For this use case, SQL Server offers a separate Import and Export Data (64 bit) tool.

Import and Export Tool Welcome Screen

After the start, the tool will prompt you for a data source from which the data should be exported. Here you choose ".Net Framework Data Provider for Odbc", which is another generic bridge driver that allows to use ODBC drivers. It’s interesting that in this tool, Microsoft doesn’t offer the "Microsoft OLE DB provider for ODBC drivers” bridge that is used for the definition of linked servers. However, as you’ll notice, this bridge driver also works fine.

On the next screen, you fill out the connection properties for the ".Net Framework Data Provider for Odbc". In this example, you just have to insert the parameter ConnectionString and the tool will automatically split this string into subproperties. The form of the ConnectionString is like an ODBC connection string. For the ODBC driver, the simplest form is the following:

Dsn=<DSN>;Driver={ODBC for ABAP};uid=<User Name>;pwd=<Password>

After this, the export/import tool will ask you for a destination database. In this example, let’s assume that you want to import data into a local SQL Server database. Therefore, you choose the "SQL Server Native Client 11.0 driver" here and enter the local server name and the name of a local test database (“testdb”).

On the next screen, the import and export wizard will ask you if you want to export data from tables or views or if you want to provide your own SQL query to extract data. In our tests, the ".Net Framework Data Provider for Odbc" seems to have had problems to extract the available tables or views from the ABAP system. So, we chose the second option to extract data. Since we have the option to use a simple “SELECT *” query here, this serves the same purpose.

As an example, we have used a simple “SELECT * FROM SYS.VIEWS” query on the next screen.
If you want to use more complex queries, you can refer to the blog SQL Queries on CDS objects Exposed as SQL Service | SAP Blogs, which describes the SQL dialect for ABAP SQL services in more detail.

As mentioned in the blog above, the ODBC driver for ABAP might return decimal floating-point data (the driver-specific type SQL_DECFLOAT). This data type is not supported by SQL Server. So, if your query contains such data types, you need to insert explicit type casts in the query (for example, to a fixed-point decimal type that can hold the returned data) to enable SQL Server to import the data. This is not necessary in our example here, though: Our simple “SELECT * FROM SYS.VIEWS” query returns only varchar-like data types and can be consumed without problems.

If you want to import into a local SQL Server table, you must specify an existing table on the next screen or you can create a new one:

To do this, click on Edit Mappings. On this screen, you can define a destination table and adapt the target column types. You might also want to set the Nullable flag for columns that could contain NULL values.

Now choose OK > Next > Next. You can ignore the warnings and run the query immediately by choosing Run Immediately, followed by Next and Execute. In our case, we transferred 623 rows from ABAP, through SYS.VIEWS, into a SQL Server target table.

Please note that this export and import wizard creates an SSIS (SQL Server Integration Services) package, which can be run directly or saved and scheduled to run at a different time.

Conclusion

For all fans of Microsoft SQL Server and ABAP: This blog describes how to access exposed CDS entities in an ABAP database from SQL Server. Using a linked server for SQL Server, you can execute queries on an ABAP CDS entity, and you can even directly join data from a CDS entity with local SQL Server data. Using the export and import wizard, you can extract data from ABAP into a local SQL Server table or other targets.

Please let us know how this works for you and please don’t hesitate to post questions and additional hints here or to contact us directly.