|
robvolk
Most Valuable Yak
USA
15559 Posts |
 Posted - 03/12/2002 : 11:41:57
|
quote:
We thought about an asynchronus method: The External programm would do lookup of special table. If ther is an entry. It does its job eand writes back the results from the erp-system. But this should be the last solution. Its a bad and faulty architectrue...
That's an absolutely solid method, because it allows you to queue processes as needed and process them. If you used instant triggering methods (say through a SQL Server trigger or a stored procedure), and the external call has some overhead, multiple calls could bog down the entire server while each call is handled. Doing it with a queueing table of some kind at least allows you to hand off the external calls to a separate execution thread, which can be terminated individually if needed without affecting the rest of the server.
How does the ERP system get called? Does it use COM or some ActiveX methods? It's not clear how this is done, so it's a little difficult to recommend a method.
If it does use COM, you can instantiate the object using the sp_OACreate method, and the additional sp_OAMethod, sp_OASetProperty, etc. system procedures to call and manipulate the object directly from a SQL Server stored procedure. The only downside is, as mentioned, that these are synchronous calls and any excess overhead could cause the process to bog down or fail.
Take a look at these articles:
http://www.sqlteam.com/item.asp?ItemID=5003
http://www.sqlteam.com/item.asp?ItemID=5908
Both involve email procedures; the first one demonstrates the use of sp_OACreate, the second describes a queueing system using tables to store each message, and the procedure that processes each. Unless you require absolutely instantaneous updates, it might be better to queue requests and process them on a schedule (5 minutes, 1 minute, etc.)
HTH
|
 |
|
Call SAP from Stored Procedure
Forum Locked
