topic Re: access to external SQL database in Application Development and Automation Discussions

access to external SQL database

Former Member — Fri, 29 Jan 2016 11:15:25 GMT

Hi,
I'm beginner in that kind of action so that's why I'm asking there help to masters

My need is : I must connect in an external database (other company) and insert data in several tables!

I have an access to that DB in display with "Microsoft SQL Server 2012" so I'm able to check inside the table if lines exist...

But now, my issue is how to connect on this database and do the insert in one table (+check if the insert was well done) ??? 😕

to access via MS SQL server i use things like

- server name

- ID

- password

is that a way to exectute I don't know, a kind of SQL statement with inside the notion of connection to the external database + the insert X Y Z into table Where blablabla... ?

Thanks in advance for your help.

Pascal.

Re: access to external SQL database

leonardo_buryanteixeira — Fri, 29 Jan 2016 12:37:23 GMT

Hi,

You can use SQL syntax.

These statements define an area in an ABAP program in which one or more Native SQL statements are to be carried out. The area between EXEC and ENDEXEC is not completely checked by the syntax check.

Here is a small example in DEMO_NATIVE_SQL report.

Re: access to external SQL database

Former Member — Fri, 29 Jan 2016 12:41:29 GMT

Hi Pascal,

While i personally dont like the idea of connecting directly to a 3rd party database and doing updates/changes from a data control perspective, this is possible.

Take a look at this SCN thread

But just a suggestion it would probably be safer for the other system if you did a ftp/sftp of a flat file or even a SOAP call so that the other system could then handle the update of it's own database and ensure that nobody tries to update fields their not supposed to while also making it easier for them to update their system without fear of connections instantly failing due to fields being changed.

Just a thought that may make it less risky to the downstream system,

Hope this helps,

Geoffery

Re: access to external SQL database

SilvioMiranda — Fri, 29 Jan 2016 12:41:51 GMT

In ECC 6.0 we have DEMO_EXEC_SQL as an example.

Re: access to external SQL database

Former Member — Fri, 29 Jan 2016 13:18:06 GMT

thanks for the example (DEMO_EXEC_SQL).

I had a quick look but nothing around the connection to the other system... isn't it?!

It's more this point which bring me some interrogations ... and not the "insert" SQL syntax

I'll have a look deeper to see if there's something around a connection to 1 other syst.

Re: access to external SQL database

Former Member — Fri, 29 Jan 2016 13:19:40 GMT

I know it's not really nice ... but it's a customer who impose me that ... so no choice

(he's working like that since a long time and won't change for me )

But it's not his finale DB but a template one (and then, he transfer the templ into the final one himself I suppose)

Re: access to external SQL database

Former Member — Fri, 29 Jan 2016 13:21:17 GMT

Fair enough, guess some people really dont care bout the risk.

Hope that link with the configuration and code sample works for you then

Geoffery

Re: access to external SQL database

SilvioMiranda — Fri, 29 Jan 2016 14:42:58 GMT

To create a conection with another DB you must set up configuration in transaction DBCO..

Take a look at this sap notes: 178949, 323151

Re: access to external SQL database

Former Member — Fri, 29 Jan 2016 14:45:50 GMT

not exists in my ECC6 syst...

Re: access to external SQL database

Jelena_Perfiljeva — Fri, 29 Jan 2016 22:59:02 GMT

Pascal, everyone can search on Google, beginners including. When I go to Google and type in "connect external database site:sap.com" or "insert to external database site:sap.com" it brings up very many posts. Native SQL is also covered in ABAP Help.

I'd suggest to ask your Basis admin to establish the DB connection. In case your SAP system does not run on Windows/SQL (ours is on Linux/Oracle, for example) there is a bit more work needed.

Overall agree with Geoffery's suggestion that it's not a good idea in general (and be prepared for a slow performance) but alas, some occasions call for it.