2

I am trying to connect my SQL Server directly to the SAP backend database so that I don't have to extract the data everyday (for fresh data) using SSIS packages.

Instead I want to create views that will access this data directly (direct querying) and get refreshed periodically.

Can someone give me a link or show/tell me the steps on how to do this?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
qwerty
  • 887
  • 11
  • 33

2 Answers2

1

If you know the hostname of the backend server, you can do something like this: SELECT TOP 1 * FROM hostname.databasename.dbo.tablename

cChacon
  • 184
  • 1
  • 8
  • How do i connect the remote SQL Server Database to the backend SAP database. – qwerty Oct 25 '17 at 19:57
  • @querty, use **sp_addlinkedserver** and **sp_dropserver** (once you are done using the linked server) to create a connection to the remote database. Follow the instructions in the following article from the section titled, _Using Transact-SQL_, (near the bottom of the article): [Create Linked Servers (SQL Server Database Engine)](https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine) – cChacon Oct 26 '17 at 14:14
0

If you want to access your data from external SQL server directly in SAP you can define connection to external database in transaction DBCO: DBCO | New Entry

Then in your ABAP code you can write SQL statements using OPEN SQL:

EXEC SQL. 
  CONNECT TO my_new_connection. 
ENDEXEC. 
...
EXEC SQL. 
  SELECT * INTO TABLE :lt_internal_table FROM dbtable. 
ENDEXEC. 
...
EXEC SQL.
  DISCONNECT my_new_connection.
ENDEXEC.

or using Classes CL_SQL_CONNECTION, CL_SQL_STATEMENT. An Example program ADBC_DEMO show how to use it.

Wojciech Górniak
  • 93
  • 9