Want to improve this question? Update the question so it focuses on one problem only by editing this post.
Closed 3 years ago.
I have an application which uses SQL Server 2008 to store data that needs to feed a bunch of financial information from SAP (which in this company uses Oracle 11 as back end).
I asked the SAP guys for access to read the tables, they said no way.
I asked them to connect to my database directly to fill my tables, they said no way.
(I have a feed from an Oracle database, other application different than SAP, using the transparent gateway with no problem, so that's why asked this)
They say the only way to interact with SAP is through the use of Web services. But due the enormous amount of data, I don't think that's the path.
Does anybody has experience in creating an interface between SAP and a SQL Server?
I'm going to extend my explanation. My current application is already being fed by three other applications (this is a big enterprise) that all go to the same tables where I receive the data to further process. All the other applications (1 Oracle, 2 SQL Server) perform either a direct update or stored procedures that save the data. Those other 3 applications are managed by different teams with no relation to each other. All of them complained at the beginning but at the end all accepted to export their data to this application.
So, the ideal would be to convince the SAP guys to do the same: Open a connection to SQL Server and perform some inserts or run stored procedures.
The basic question is - push or pull? You can either have some kind of setup that makes the SAP R/3 system periodically export some data that you then grab, or you could have some external system reach out and grab the data. As for "the only need to interact with SAP is through the use of Web services" - technically speaking, that's as close to feces of the cow's partner as you'll get. There may be non-technical issues involved - weird policies or unwilling system administrators - but let's focus on the details.
As for a "push" solution, you could either rig one of the existing reports to be run periodically and have the output saved to a file or mailed somewhere. That is relatively easy to do, but it requires that there are reports that give you the data you want. If they aren't there, write down the specs and program them or have someone program them. It's no magic, just another programming language.
If you want to pull, you could go for the database directly, but I'd strongly discourage it. As soon as you hit your first cluster table, you'll have reached a dead end. If you're thinking about large quantities of data, the best way to pull them would probably be to establish an RFC connection (proprietary protocol, see lots of other questions here) and either use a BAPI if one exists (that's a programming interface that is released for official use and maintained by SAP) or use RFC_READ_TABLE to access the data. Be aware that the latter is not usable for all tables, it depends on the structure of the table (total size and field types). Web Services just add another level of encoding, and a noisy one - not very suitable for high-volume ETL processes.
I've done this a few times over the years, and received basically the same response from SAP teams that you got. In almost every case, the solution wound up being flat file "reports" generated by SAP that we loaded into SQL Server using SSIS packages. The trick is making sure the reports are designed properly. I've seen SAP teams include "default" filters in the creation of these reports that they failed to mention until after the process was live and data was missing. Good luck.
We run SQL rather than Oracle, and had a similar issue. We were told that directly accessing the DB layer of an SAP system would void the warranty. So we asked SAP directly - they are OK with doing selects on the DB so long as you don't update, insert, or delete without going through the App layer. If you do update, insert, or delete outside of the App layer you'll void your warranty, so that is a non-starter. You can have your SAP team open an OSS message if they'd like the same answer.
That said, if this is an ongoing interface rather than a one time data load, I would just have someone build you an RFC, a web service, or an ongoing extract to grab the data from the app.
As you would know for such application interface the best option depends upon a number of factors like data volume, mode of interface desired real time vs. batch, synchronous vs asynchronous. If the data volume is large and you do not need real time updates to your application then running a report on SAP side and generating a data file for uploading to your application should be acceptable option. For real time updates IDocs (multiple data files) can be used. Otherwise you can use standard SAP APIs (BAPI) for pulling the information that you need from SAP. You can also have the SAP guys create a custom RFC for function RFC_READ_TABLE and use SQL like query to get the data from SAP.
It is possible to update your application database directly using Native SQL in ABAP program on SAP side. I have done it for MS-SQL server and do not see any limitation to use it for Oracle too.
