Let us see how we can connect an External MSSQL DB and access it via a simple ABAP Program.
SAP is running on Windows and the required DLL is already present / installed.
The MSSQL DB is on the Same Network where SAP is residing.
First we need to Create a DB Connection for the External MSSQL DB in SAP.
Goto DBCO, Create a New Entry and enter the details.
DB Connection – Give any name, which will be used later in the ABAP Program.
DBMS – Select “MSS” as we are going to access MS SQL.
User Name – User Name to connect with the MS SQL – will be provided by your DBA.
DB Password – Password to connect to the DB, enter same password in both the fields.
Conn. info – Syntax is MSSQL_SERVER=HostName MSSQL_DBNAME=DataBaseName. You can get this details from DBA.
Once, the entries are done, Save and Exit.
Now, we shall see how we can access the Data using an ABAP Program.
Goto SE38 and Create an Executable Program.
DATA: user_id TYPE String.
EXEC SQL. ” All statements between EXEC SQL and ENDEXEC are SQL Statements
CONNECT TO ‘DB_CONXN_NAME’ ” DB Connection Name, Note there is no PERIOD in the SQL Statements
SELECT field_name into :user_id from Table_Name where first_name = ‘Subu’ and last_name = ‘Subu’
DISCONNECT :’DB_CONXN_NAME’ “Disconnecting the Connection
WRITE: / USER_ID.
The above Program will fetch the User id from the external MS SQL DB and Displays it.