Exporting Data to external database

[Former Member]

Hi All,

As background our SAP systems use Oracle on Solaris.

I have written programs that export data to an external oracle database also running on solaris by embedding native SQL in the ABAP program.

There is now a requirement to export data to a MS SQL Server database running on Windows.

We are on BW v3.5. I know this was not possible previously can anyone confirm whether the method above will still not work for a SAP system running on Solaris exporting to MS SQL Server on Windows? i.e. Native SQL in ABAP program.

Many Thanks

David

[Former Member]

Hi

Have you try to use DBlink?

Here you can create a link between SAP and MS SQL and use native SQL.

Max

[Former Member]

Max,

No I haven't heard of it before.

Is there a transaction in SAP to use it?

Thanks

Daivd

[Former Member]

I don't know very well it, but it's a tool to link SAP to external database.

No u can create a link in the table DBCON and then insert native SQL code in ABAP prog. using EXEC SQL statament.

I used it only once, I hope it can be usefull for u.

Try to see this note: 178949<a href="https://websmp230.sap-ag.de/sap(bD1pdCZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=0000178949&nlang=E">https://websmp230.sap-ag.de/sap(bD1pdCZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=0000178949&nlang=E</a>

Anyway this the text:

Symptom

DATABASE MULTICONNECT WITH EXEC SQL

This note describes the possibility of accessing data with EXEC SQL which is not on the R/3 database server but on another MSSQL system.

Other terms

CONNECT DBCON OLEDB dblib

Reason and Prerequisites

It's desired to make a connection to a SQL Server database running on a server different from the R/3 server.

Prerequisite: At least one Windows application server must be available on the R/3 system and the DBSL dynamic library (DLL) dbmssslib.dll must be installed on this server. This dll can be downloaded from the SAP Service Marketplace.

Solution

Basics of the EXEC SQL MultiConnect

OPEN SQL database accesses always refer to the R/3 database for the particular R/3 system's <SID> (three letter uppercase database name). EXEC SQL accesses normally also refer to the R/3 database. To access data which is on the R/3 database server in a database other than the <SID> database, it is sufficient to enter the table name qualified by the database name in EXEC SQL.

Example:

EXEC SQL.

SELECT MYFIELD FROM MYDB..MYTABLE

ENDEXEC.

Furthermore, you have the option in R/3 to access data with EXEC SQL which are on other SQL Server systems.

Creating a DBCON entry for a remote connection

Every link which should be addressed via the Multiconnect must be described by a record in table DBCON. This entry can be created by means of the transaction sm30. For SQL Server create an entry as follows:

CON_NAME: <my_conn_name>

DBMS: MSS

USER_NAME: <db_user>

PASSWORD: <password>

CON_ENV: MSSQL_SERVER=<server_name> MSSQL_DBNAME=<db_name>

db_user could be sapr3 for example if the server is running R/3. The password field may be blocked from view by asterisks in sm30. USER_NAME can be left blank in order to use integrated security (4.6d kernel and later). In that case make sure that the OS User running R/3 has the required privilege on the remote server. A dummy password must be entered in sm30, it will not be used when the user name is blank.

Specifying the server name

The server name is simply the name of the SQL Server or named instance i.e. the hostname (for a default instance), or <host>\<instname>

Starting with release 6.40 however there are some special considerations that should be taken into account. When running with kernel release 6.40 or later, the R/3 system will prepend a protocol specifier to the server name. Example:

1. For a server running on the same server:

np:<servername>

2. For a remote server:

tcp:<servername>

This forces the connection to be made with a named pipes and tcp/ipprotocols respectively.

When specifying the server name you can override this by setting your own protocol (tcp: or np:), or you can make R/3 avoid setting any prefix by using simply :<servername> - a colon in front of the server name.

How to use the Multiconnect

Here is an example for how to connect to another server with CON_NAME=BSK. The example assumes BSK is running R/3:

EXEC SQL.

CONNECT TO 'BSK'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'BSK'

ENDEXEC.

EXEC SQL.

SELECT db_name() INTO :DBN FROM SVERS

ENDEXEC.

WRITE: / 'current database name', DBN.

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

EXEC SQL.

SELECT db_name() INTO :DBN FROM SVERS

ENDEXEC.

WRITE: / 'current database name', DBN.

Tools for creating DBCON entries

In SAP basis release 6. 40 new tools were introduced to make it easier to create DBCON entries for use in remote monitoring. These tools are accessible through st04_mss -> "Change connection data" button -> Go To -> "Maintain DBCON" for example.

The "Change connection data" button is the small pencil icon on the right hand side of the main st04_mss screen.

There is an on-line guide for creating DBCON entries this way. It is available on:

http://help.sap.com

Documentation -> SAP NetWeaver -> SAP NetWeaver '04

And then expand the tree on the left hand side using SAP NetWeaver -> Application Platform -> Databases -> SAP/MS SQL Server 2000 DBA in CCMS.

Finally go to Database Management Tools -> Monitoring Remote SQL Server Databases -> Setting Up the Remote Connection -> Creating a DBCON Entry.

Max

[Former Member]

Thanks Max, I have used this before, I didn't know it was called DBLInk I just knew it by the transaction code DBCO.

I don't think this will solve my problem because we are on Solaris and the help text prerequisite says

"At least one Windows application server must be available on the R/3 system and the DBSL dynamic library (DLL) dbmssslib.dll must be installed on this server."

We do not have any of our SAP systems running on Windows.

Many thanks

David

[Former Member]

Hi

I'm very sorry

Perhaps you're right, I believe the correct name is DBconnect (not DBlink).

U should try to open a request to SAP

Max

[Former Member]

Max,

Thanks, I appreciate you trying to help.

Regards

David.