EXEC SQL

Select data from non-SAP system DB.

1) Create connection entry in table DBCON, you will need to have a DB userid and password and the connection string.
con_name = descriptive name
dbms = mss
user_name = sql_userid
password = ****
con_env = MSSQL_SERVER=mather-mri-002 MSSQL_DBNAME=MRIAccess

ABAP:
2) Establish connection:
data: con_name like dbcon-con_name value 'MRI'.
* Connect to external DB
EXEC SQL.
CONNECT TO :con_name
ENDEXEC.
EXEC SQL.
SET CONNECTION :con_name
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Unable to connect to' con_name.
ENDIF.

3) Sample Code to Select Data:

* Drop subquery tables
EXEC SQL.
IF EXISTS(SELECT TABLE_NAME FROM MRIACCESS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'ZSAP_ACCTN')
DROP TABLE MRIACCESS..ZSAP_ACCTN
IF EXISTS(SELECT TABLE_NAME FROM MRIACCESS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'ZSAP_SAPCO')
DROP TABLE MRIACCESS..ZSAP_SAPCO
IF EXISTS(SELECT TABLE_NAME FROM MRIACCESS.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'ZSAP_ENTIT')
DROP TABLE MRIACCESS..ZSAP_ENTIT
ENDEXEC.

* Create subquery tables
EXEC SQL.
CREATE TABLE MRIAccess..ZSAP_ACCTN ( acctn VARCHAR(9))
CREATE TABLE MRIAccess..ZSAP_SAPCO ( sapco VARCHAR(4))
CREATE TABLE MRIAccess..ZSAP_ENTIT ( entit VARCHAR(6))
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to create subquery tables'.
ENDIF.
* Populate subquery tables
LOOP AT s_acctn.
EXEC SQL.
insert into MRIAccess..ZSAP_ACCTN values (:s_acctn-low)
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to populate subquery tables'.
ENDIF.
ENDLOOP.
LOOP AT s_sapco.
EXEC SQL.
insert into MRIAccess..ZSAP_SAPCO values (:s_sapco-low)
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to populate subquery tables'.
ENDIF.
ENDLOOP.
LOOP AT s_entit.
EXEC SQL.
insert into MRIAccess..ZSAP_ENTIT values (:s_entit-low)
ENDEXEC.
IF sy-subrc <> 0.
MESSAGE e009 WITH 'Failed to populate subquery tables'.
ENDIF.
ENDLOOP.

* Now select data from extrnal DB
* All records not extracted
EXEC SQL PERFORMING append_itab.
SELECT g.acctnum, e.saporder, e.sapco, e.EntityID, j.entrdate,
j.amt, j.descrpn, j.ref, j.Item into :itab
FROM
MRIPROD..ENTITY e
INNER JOIN MRIPROD..JOURNAL j ON j.ENTITYID = e.ENTITYID
INNER JOIN MRIPROD..GACC g ON j.ACCTNUM = g.ACCTNUM
WHERE
j.source = :p_sourc AND
g.acctnum NOT IN (select acctn from MRIAccess..zsap_acctn) AND
e.sapco NOT IN (select sapco from MRIAccess..zsap_sapco) AND
e.entityid NOT IN (select entit from MRIAccess..zsap_entit) AND
j.basis = :p_basis AND
j.dtsapul IS NULL
ENDEXEC.
IF p_update = 'X'. "Update
EXEC SQL.
UPDATE MRIPROD..JOURNAL
set dtSAPUL = :v_now
FROM
MRIPROD..ENTITY e
INNER JOIN MRIPROD..JOURNAL j ON j.ENTITYID = e.ENTITYID
INNER JOIN MRIPROD..GACC g ON j.ACCTNUM = g.ACCTNUM
WHERE
j.source = :p_sourc AND
g.acctnum NOT IN (select ACCTN from MRIACCESS..ZSAP_ACCTN) AND
e.sapco NOT IN (select sapco from MRIAccess..zsap_sapco) AND
e.entityid NOT IN (select entit from MRIAccess..zsap_entit) AND
j.basis = :p_basis AND
j.dtsapul IS NULL
ENDEXEC.
ENDIF.
*&---------------------------------------------------------------------*
*& Form append_itab
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM append_itab.
APPEND itab.
ENDFORM. " append_itab

4) Close or reset connection
** Reset to "default connection"
EXEC SQL.
SET CONNECTION DEFAULT
ENDEXEC.