Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-12 : 09:39:41
|
| Michael writes "We developed an C/S App with Access-Frontend and MS SQL-Backend. Now we need to send a request to an external ERP_System out of a strored procedure. The call to the ERP-System is not the problem, but how can we trigger that programm?We need a call of our programm triggered by an event on the SQL Server.How is such a task archived?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...Any hints?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-12 : 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=5003http://www.sqlteam.com/item.asp?ItemID=5908Both 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