如何在ABAP中使用Native SQL访问SQL SERVER外部数据库

wangyuzhen

最初由 hjy2000 发布
[B]汗 楼主  05-12-27 11:59 [/B]

我也没注意到，呵呵

ryq0000

1. 工作环境需求
A. The operating system user adm must be able to log on to the secondary database.
B. The R/3 table DBCON must be maintained.
C. The correct database library 'dboraslib.' must exist in the kernel directory.
Connect to SQL Server database 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.
dbmssslib.dll
2. 配置连接信息
配置TCODE: dbco
任何配置信息都存储在DBCON表中.
配置示例:
A. 访问MSSQL的配置示例(参见SAP Notes 178949)
CON_NAME:
DBMS: MSS
USER_NAME:
PASSWORD:
CON_ENV: MSSQL_SERVER= MSSQL_DBNAME=
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.
B. 访问ORACLE的配置示例()
Connection name logical name of the connection
DBMS ORA
User name Oracle users
DB password password for the above-mentioned Oracle user
Conn.info TNS alias
Permanent Usually should not be set for a secondary connection.

3. 程序中如何使用
report zdbcon .
data: dbn(128).
EXEC SQL.
CONNECT TO 'DAN1'
ENDEXEC.
EXEC SQL.
SET CONNECTION 'DAN1'
ENDEXEC.
EXEC SQL.
SELECT db_name() INTO BN FROM SVERS
ENDEXEC.
write: / 'current database name', dbn.
DAN1在DBCON表中配置
CON NAME DAN1
DBMS MSS
USER NAME iis_log
PASSWORD CON ENV
MSSQL_SERVER=tcp:###.###.###.### MSSQL_DBNAME=iislog

按照以下方法试了,不行啊,连接不了?


另外有个问题问大家一下,参见SAP Notes 178949,请问这个怎么看啊?
请知道的兄弟给个具体查看步骤,谢谢了.

hjy2000

SAP Notes 要道 service.sap.com 上看，前提是得有账户登录

ryq0000

哦,知道了,谢谢hjy2000.

leemin

最初由 abapstones 发布
[B]1.       工作环境需求
    A. The operating system user adm must be able to log on to the secondary database.
    B. The R/3 table DBCON must be maintained.
    C. The correct database library 'dboraslib.' must exist in the kernel directory.
Connect  to SQL Server database 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.
dbmssslib.dll
2.       配置连接信息
配置TCODE: dbco
任何配置信息都存储在DBCON表中.
配置示例:
A.      访问MSSQL的配置示例(参见SAP Notes 178949)
CON_NAME:
DBMS: MSS
USER_NAME:
PASSWORD:  
CON_ENV: MSSQL_SERVER= MSSQL_DBNAME=
   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.
B.      访问ORACLE的配置示例()
   Connection name        logical name of the connection
     DBMS                   ORA
     User name              Oracle users
     DB password            password for the above-mentioned Oracle user
     Conn.info              TNS alias
     Permanent              Usually should not be set for a secondary connection.

3.       程序中如何使用
report zdbcon .
data: dbn(128).
EXEC SQL.
CONNECT TO 'DAN1'
ENDEXEC.
EXEC SQL.
SET CONNECTION 'DAN1'
ENDEXEC.
EXEC SQL.
SELECT db_name() INTO BN FROM SVERS
ENDEXEC.
write: / 'current database name', dbn.
DAN1在DBCON表中配置
CON NAME DAN1
DBMS MSS
USER NAME iis_log
PASSWORD CON ENV
MSSQL_SERVER=tcp:###.###.###.### MSSQL_DBNAME=iislog [/B]

我以前有做过sap和外部数据库连接.
但直接是从DBCO建立连接,然后就写程序了。现在换了个公司,发现这样做的话， 会报错.是不是DBCO建立连接之前还有什么步骤要做，比如basis要设置什么的。请大家指出。谢谢。

T-McGrady1

是的啊,请高人指教一下啊.

hbf001

支持一下，
谁测试？测试结果反馈一下！

ryq0000

有BASIC的吗?搞个测试的呢.估计是要装什么的.我直接测试不成功

rockwl2001

Basis必须进行相应的设置，而且有个大前提，连Sql Server，应用服务器必须是Windows平台，其他平台别想了，这是微软的策略。*.DLL文件也只有在Windows系统下是能用的。

我以前做过一个项目，调用外部的SQL Server，但数据库和应用服务器都是AIX平台，所以又买了台Windows的应用服务器作为Dialog Instance才能用SQL Server，出此下策的原因是客户服务器也是问实施SAP的公司买的，多买台服务器SAP项目组就有更多经费了，所以......  其实我个人更推荐在SAP系统外部写个ASP或者ASP.Net 的网页或者Web Service，然后用SAP访问这个网页或Web Service就可以了，外部程序代码不会超过20行，就一个Select罢了，而且各方面好控制，成本低。

原帖由 leemin 于 2007-3-29 23:11 发表


我以前有做过sap和外部数据库连接.
但直接是从DBCO建立连接,然后就写程序了。现在换了个公司,发现这样做的话， 会报错.是不是DBCO建立连接之前还有什么步骤要做，比如basis要设置什么的。请大家指出。谢谢。

rattbell

*&---------------------------------------------------------------------*
*& Report  ZtestSP
*& Manual Extract AXML file TO A2S temp Tables.
*&---------------------------------------------------------------------*
*& Used For Test and Troubleshooting  Only!
*& Author: Cheney_cheng
*&---------------------------------------------------------------------*
REPORT  ZTESTSP.

PERFORM CONNECT_DB USING  'AG2S'.   " 链接
PERFORM DISCONNECT_DB USING  'AG2S'.   "断开连接

FORM CONNECT_DB USING AG2S.
    EXEC SQL.
     CONNECT TO :'AG2S'
    ENDEXEC.
IF SY-SUBRC = 0.
   " MESSAGE: ' 部分 ( 或全部 ) 數據链接成功 ' TYPE 'S'.
   " 对数据库进行操作
   EXEC SQL.
      exec dbo.KL_AGILE_XML2DB;
   ENDEXEC.
   IF SY-SUBRC = 0.
   MESSAGE: ' Extract Agile XML to A2S Tables Is OK! [KL_AGILE_XML2DB成功] ' TYPE 'S'.
   ENDIF.

ELSE.
   MESSAGE: ' Database connection is Failed!' TYPE 'S'.
   " 部分 (或全部) 數據链接失敗
ENDIF.

ENDFORM.


FORM DISCONNECT_DB USING AG2S.
  EXEC SQL.
    DISCONNECT :AG2S
  ENDEXEC.
ENDFORM.
" DISCONNECT_DB