Technical Articles
Access CDS Objects Exposed as SQL Services Using Microsoft SQL Server
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
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
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
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 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.
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.
Related links
Other blogs related to ABAP SQL services or the ODBC driver for ABAP:
Using the ODBC driver for ABAP on Linux | SAP Blogs ,
Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs ,
Access Control for ABAP’s SQL Service (1/3) – Set the Scene | SAP Blogs ,
SQL Queries on CDS objects Exposed as SQL Service | SAP Blogs
Links to SAP documentation:
ODBC – ABAP Platform, On-Premise (Latest Version)
ODBC – SAP BTP, ABAP Platform (aka Steampunk)
ODBC – S/4HANA Cloud (Latest Version)
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.
Hi Frank-Martin,
this looks really interessting and I already have a use cae in mind. But what I remember from licensing discussions some years ago is, that such an access is actually not covered in standard (database) licenses coming with SAP software. Has that changed now?
Kind regards
Alej
Hi Alej,
the topic of this blog is the the "ODBC driver for ABAP". This driver communicates directly with the ABAP system (with ABAP credentials) and not directly with the underlying database ( with a database user). So there are no licensing problems as long as you have a proper SAP license.
However, there are some prerequisites. The "ODBC driver for ABAP" relies on ABAP backend infrastructure that is only available in Steampunk or S/4 HANA systems. The "ODBC driver for ABAP" is not able to connect to older AnyDB ERP systems.
I remember that such licensing discussions always came up in the past when customers wanted to connect directly to the underlying DB of their ERP system with the native AnyDB ODBC driver. Such connections were often not covered by the SAP OEM DB license. Also such connections can not be recommended for security reasons since they completely bypass the ABAP security. Please see also SAP Note 2511210 where problems related to direct database access are discussed in more detail. Those concerns do not apply to the "ODBC driver for ABAP".
Regards
Frank