178949 – MSSQL: Database MultiConnect

Home » Uncategorized » 178949 – MSSQL: Database MultiConnect
September 10, 2014 Uncategorized No Comments
Symptom
Database Multiconnect

This Note describes the possibility of using SAP ABAP to programmatically access data which is not in the local system database but in a remote MSSQL database.

If you wish to connect to a remote SQL Server system purely for system and performance monitoring purposes, see SAP Notes:

1316740 – to use DBACockpit alone
1458291 – to use Solution Manager 7.1 (partially based on DBACockpit)
1388700 – to use Solution Manager 7.0 EHP1 (partially based on DBACockpit)

If you wish to connect to a remote SQL Server system to extract data to load into SAP BW, refer to SAP Note 512739.


Other Terms
CONNECT DBCON OLEDB ODBC dblib


Reason and Prerequisites
It is desired to make a connection to a SQL Server database running on a server different from the R/3 server.

The following connections to SQL Server are supported using the DBCON method:

  • The OLEDB DBSL version is used in all kernels 6.40 and earlier.  It is also used in kernel versions 700 and 701 running on SQL Server 2005 or SQL Server 2000.  The OLEDB DBSL will support connections to the following SQL Server versions:
  • SQL Server 2000 and higher.  Earlier versions (7.0 and 6.5) are not supported.
  • The OLEDB DBSL is only supported on Windows.  No other operating system can be used to connect to SQL Server using the OLEDB DBSL.

  • The ODBC DBSL version is used by 700 and 701 kernels running on SQL Server 2008 and later.  Also kernels 710, 711, 720 and later use the ODBC DBSL version exclusively. The ODBC DBSL will support connections to the following SQL Server versions:
  • SQL Server 2000 – except when using the SQL Server 2012 client libraries (sqlncli11).  If your system is running on SQL Server 2012 and you need to connect to SQL Server 2000 you can use a workaround described in note 1781460.  There are some limitations which should be kept in mind when using the ODBC DBSL against SQL Server 2000.  They are described in note 1665902.
  • SQL Server 2005 and higher versions are supported.  Earlier versions (7.0 and 6.5) are not supported.
  • The ODBC DBSL version can be used on Linux application servers in addition to Windows, but Linux only supports the SQL Server 2012 client, which will not connect to SQL Server 2000.  See also note 1644499.

Prerequisite: Any application server which is used for remote database access to SQL Server via DBCON must be configured as described in note 1774329.


Solution
  • 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 or transaction DBCO. 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> should be a valid SQL login on the server.   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). A dummy password must be entered in sm30, it will not be used when the user name is blank.
           IMPORTANT: The user name is case sensitive. If left blank, then “Windows Authentication” is automatically used.  It is important to remember that when using “Windows Authentication”, the user running the R/3 service is the user that must have access to the remote server. This user is usually <DOMAIN>SAPServiceSID.  You can see which user runs R/3 by checking Control Panel -> Administrative Tools -> Services, double-click on the SAP<SID>[nn] service and choose the logon tab.
           Optionally MSSQL_SCHEMA=<schema_name> may be added to CON_ENV. This means that the command
              setuser ‘<schema_name>’
           will be executed once the connection is made.  This means tables within <schema_name> will be directly accessible without specifying the schema. In this case the login (<db_user> or OS User) must be a member of the sysadmin fixed server role or the db_owner fixed database role.  See Books On-line for more information on the setuser command.

  • 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:
                lpc:<servername>
      2. For a remote server:
                tcp:<servername>
    This forces the connection to be made with a shared memory and tcp/ip protocols respectively.
    When specifying the server name you can override this by setting your own protocol (tcp: or lpc:).

    If a connection cannot be made using the prefixes after three retries each of which times out in 20 seconds, R/3 will then remove the prefix and use only <server_name>. You can stop R/3 from inserting its own prefixes by using

    MSSQL_SERVER=:<servername>

    This will speed up the connection a lot if for example tcp:<servername> doesn’t work but <servername> does.

    A port number can be added to the server name:

    <servername>,<port number>

    This is necessary if no SQL Browser is running on the target database server.

  • Registering a change in DBCON
    When changing existing DBCON data, sometimes the new connection information does not appear to be having an effect.  This is because the SAP workprocess holds on to connections for re-use as long as they are not explicitly closed.  In many cases it is therefore necessary to either kill the workprocess or to restart the application server before changes in DBCON are truly in effect.
  • How to use the Multiconnect
    Here is an example for how to connect to another server with CON_NAME=BSK.  The example assumes that BSK refers to a server and database which contains SAP tables:

    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 NetWeaver ’04 tools were introduced to make it easier to create DBCON entries for use in remote monitoring for SQL Server.  These tools are accessible through transaction 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.
  • In NetWeaver 7.00 or later, the transaction DBACOCKPIT has some maintenance screens for managing DBCON entries.  Use transaction DBACOCKPIT and select “DB Connections” at the top of the launchpad on the left hand side.  See also note 1316740.

           


Header Data
Released On 29.10.2012 23:19:41
Release Status Released for Customer
Component BC-DB-MSS Microsoft SQL Server
Priority Recommendations / Additional Info
Category Consulting


Validity

This document is not restricted to a software component or software component version 

References
This document refers to:

SAP Notes

1781460   Client unable to establish connection to SQL Server 2000
1774329   Preparing your SAP instance to connect to remote SQL server
1644499   Database connectivity from Linux to SQL Server
1458291   SolMan 7.1 Database Warehouse & Alerting for MSSQL
1388700   SolMan 7.0 EHP1 Database Warehouse for MSSQL
1316740   Set up remote monitoring for Microsoft SQL Server databases
1248222   ODBC DBSL profile parameters and connect options
829706   MSSQL: DB Multiconnect in the MSSPROCS program
512739   BW external DB Connect for MS SQLServer
323151   Several DB connections with Native SQL
160484   DB2/390: Database multiconnect with EXEC SQL
146624   IBM i: Database Multiconnect for IBM DB2 for i
117261   Multiconnect and DB procedure enhancements for 4.0B
44977   EXEC SQL: New features
This document is referenced by:

SAP Notes (15)

555223   FAQ: Microsoft SQL Server
512739   BW external DB Connect for MS SQLServer
1781460   Client unable to establish connection to SQL Server 2000
1774329   Preparing your SAP instance to connect to remote SQL server
1248222   ODBC DBSL profile parameters and connect options
1644499   Database connectivity from Linux to SQL Server
1388700   SolMan 7.0 EHP1 Database Warehouse for MSSQL
829706   MSSQL: DB Multiconnect in the MSSPROCS program
1458291   SolMan 7.1 Database Warehouse & Alerting for MSSQL
1316740   Set up remote monitoring for Microsoft SQL Server databases
323151   Several DB connections with Native SQL
160484   DB2/390: Database multiconnect with EXEC SQL
117261   Multiconnect and DB procedure enhancements for 4.0B
146624   IBM i: Database Multiconnect for IBM DB2 for i
44977   EXEC SQL: New features

LEAVE A COMMENT