topic Re: Read Tables from SQL Server in Application Development and Automation Discussions

Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 05:09:16 GMT

hi i have to read certain tables from our local SQL Server how can i do that, can somebody explaing wid example.

thanx

abhishek suppal

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 05:38:41 GMT

Hi, I think you want to read a table directly from DataBase Layer, not from R3, right?

You can reference following link:

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm

Re: Read Tables from SQL Server

athavanraja — Sat, 30 Jul 2005 05:53:59 GMT

check out OSS Note No. 323151

Regards

Raja

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 05:54:07 GMT

No that's not wat i want , we have SAP server on ORACLE and i want to read tables from our local SQL Server no that of SAP, we r running some applications on our SQL, so i want to read those tables and want to update in SAP.

i hope i have cleared my query.

abhishek

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 06:21:01 GMT

Hi, I understand what you mean now.

You want to read data from a External SQL SERVER from SAP, right?

Then in my opinion, three way can achive it.

1. XI you can create a scenario in XI like this, create a RFC in your R3, as a request interface. In XI, just mappinig the input parameter from RFC, and transform them to be a JDBC request to SQL SERVER, get them from it and through a return interface to the R3. As it is a synchronous scenario, maybe BPM is necessary.

2. Dotnet Connector. As you want to read data from SQL SERVER, install a Dotnet Connector on SQL SERVER side. Configure a RFC call server on its side, also creat a RFC on your R3 side. Create the RFC connection between them.

Then when the R3 need data, it can call the RFC remotely to the Dotnet(SQL SERVER) side, get data from them.

3. You can excute external command from R3 though some FM, like CL_GUI_FRONTEND_SERVICES-EXECUTE, WS_EXECUTE and so on. Though them, you can run some tool of SQL SERVER to get the data in a console. Next, just save data into a file, and read the file into R3. If you can directly store them into R3 looks better, but I'm not very familiar the tools of SQL SERVER.

That's my opinion, hope it will be helpful and productive.

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 06:34:56 GMT

You need to maintain the SQL Server connection details in table DBCON.

Further, you can use the connection id (say given as 'ID' )to connect using native sql.

e.g.

EXEC SQL.

connect 'ID'.

END EXEC.

cheers,

Ram

Re: Read Tables from SQL Server

athavanraja — Sat, 30 Jul 2005 06:38:02 GMT

check out the oss note i mentioned (323151). It has got the clear explanation.

Regards

Raja

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 06:39:04 GMT

thanx

but is there not a function or rfc thru which i can directly read tableof my EXTERNAL SQL Server, one way is that i run SQL Server tool and create some txt file thru it and then in SAP write program to read that file and update my SAP tables, that can be done i know, but i don't want to put any middle layer i want to read directly SQL tables thru ABAP.

abhishek suppal

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 06:40:13 GMT

Raja,

i have also tried using DBCON but i am not able to do so as it gives dump i don't know why.

abhishek suppal

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 06:46:37 GMT

it means that your connection is not being established.

Once it is done you can use SQL db's sql statements to select data.

But first thing you need to ensure that you are able to connect to the sql db.

you should check the connection entries in DBCON to see if it it proper.

Also, there might be some config requierd at SQL databse end to allow connection.

The OSS notes on this should have all the info.

Also did you check if SQL server is accessible from SAP.

first check - Ping sql ip.

Cheers,

Ram

Message was edited by: Ram Manohar Tiwari

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 07:02:27 GMT

i have used the following code and also check SQL Server is piginging

in DBCO

i have entered in ADMIN

database conn : ADMIN

DBC = MSS

user name = sql_userid

passqord = sql_pwd

connection information : MSSQL_SERVER=192.16.128.54\STAR;MSSQL_DBNAME=ADMIN

REPORT ZCONNECTION .

perform remote_access using 'ADMIN' .

FORM remote_access USING con_name LIKE dbcon-con_name.

Test if connection 'con_name' has already been opened

EXEC SQL.

SET CONNECTION :con_name

ENDEXEC.

IF SY-SUBRC <> 0.

Connection not yet opened.

EXEC SQL.

CONNECT TO :con_name

ENDEXEC.

IF SY-SUBRC <> 0.

error handling

ENDIF.

Do something on connection 'con_name'

EXEC SQL.

...

ENDEXEC.

Commit the changes on 'con_name'

EXEC SQL.

COMMIT.

ENDEXEC.

Reset to "default connection"

EXEC SQL.
SET CONNECTION DEFAULT
ENDEXEC.

ENDFORM.

its giving dump

Error setting up a secondaty database connection

abhishek suppal

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 07:37:13 GMT

I am not sure if sy-subrc used in your program is actually going to provide you proper information.

I don't think you can belive sy-subrc here ..

Try just connecting first ,rather than checking set connection and all...

just use connect stmt and then a simple select stmt.

Re: Read Tables from SQL Server

Former Member — Sat, 30 Jul 2005 08:55:11 GMT

dear ram,

i still get the same error

abhishek

Re: Read Tables from SQL Server

Former Member — Mon, 01 Aug 2005 14:33:27 GMT

can ny one help

Re: Read Tables from SQL Server

Former Member — Tue, 02 Aug 2005 05:45:46 GMT

help

Re: Read Tables from SQL Server

Former Member — Mon, 12 Mar 2007 18:01:04 GMT

i am having the same dump problem, but after reading posts. i have learned that you need to install the database library of SQL server but prerequisite is that you have at least one Windows OS application server as the dll is a windows file.

our basis has downloaded the file from marketplace i am still awaiting for it to be installed so that i could simulate. I believe this should solve the dump problem.