May 7, 2002 at 7:41 pm
I would like to create a scheduled stored procedure that queries the data base, produces an XML document, sends that XML doc to a remote server, receives an XML doc back, parses the return doc and updates the database with returned info. Any guidance? What's the best way to approach this?
May 7, 2002 at 9:05 pm
Im not sure I would try to accomplish all of that with TSQL in a stored procedure. You could however write a vb application or even a vb script to do this and schedule it to run as a sql job. Then you could build a stub stored procedure that would just kick off the sql job. I do have a few questions and comments though: When you say send a XML doc to a remote server, is this a sql server, web server or some other server. I assume that it knows what to do when it gets the xml file, or is this part of the solution too? The sending of the xml can be done with the xmlHttp object or it could be done via SOAP and web services, or it may even be just an xml query to sql. The next question would be does this need to be done in a transaction and how long do you estimate that a complete cycle would take?
Also is the remote server accross the internet, a different subnet or just somewhere else on the same subnet? What type of data connection is between the source server and the remote server?
May 8, 2002 at 5:09 am
Or you could try doing it in a DTS package using ActiveScripting the same way Leon speaks of doing with VB.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 8:29 am
We are building a mobile asp.net/vb.net application with a SQL Srvr 2K DB that creates Purchase Orders (among other things). We would like to send these PO's to our back office SAP system (on a periodic basis - e.g., every 10 minutes) via Web Methods (actually it's SAP's version called Business Connector).
We need to extract the appropriate data from the DB, format an XML doc, post the doc to the URL for the Web Methods server, receive the immediate response from Web Methods in the form of a return XML doc that contains the SAP assigned PO number, and update our SQL Srvr Db with the PO number.
We have an SQL template file that creates the PO XML. We've researched the sp_makewebtask and xmlHttp facilities - but we're unclear (this is all new to us) on the right approach/architecture.
The Web Methods server is a separate server accessed over the internet. Web Methods knows what to do with the XML doc (we've tested this). I believe we need to do this as a transaction, i.e., wait for the return XML doc. The return time should be almost immediate - certainly within seconds.
Thanks very much.
quote:
Im not sure I would try to accomplish all of that with TSQL in a stored procedure. You could however write a vb application or even a vb script to do this and schedule it to run as a sql job. Then you could build a stub stored procedure that would just kick off the sql job. I do have a few questions and comments though: When you say send a XML doc to a remote server, is this a sql server, web server or some other server. I assume that it knows what to do when it gets the xml file, or is this part of the solution too? The sending of the xml can be done with the xmlHttp object or it could be done via SOAP and web services, or it may even be just an xml query to sql. The next question would be does this need to be done in a transaction and how long do you estimate that a complete cycle would take?Also is the remote server accross the internet, a different subnet or just somewhere else on the same subnet? What type of data connection is between the source server and the remote server?
May 10, 2002 at 6:48 am
Sounds like a good job for xmlHTTP object (not sure if its still this in ASP.net). You can get info in the XML SDK or check out some of the articles I have written. The only problem I see is how to handle the transaction. Usually in an Enterprise application in windows NT you would use "Message Queuing Services" for this. Even though you say that the response should only take a second it is not guaranteed. To do things 100% correct you need to look at this. You could probably get away with using error traping to determine if there was a problem with the response. Then you would need some way to requery for the PO number or even resend the xml PO if the remote server never got it. Its a little tricky but not too bad.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply