SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Call SAP from Stored Procedure
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/12/2002 :  09:39:41  Show Profile  Visit AskSQLTeam's Homepage
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

USA
15635 Posts

Posted - 03/12/2002 :  11:41:57  Show Profile  Visit robvolk's Homepage
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000