Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Call web service from stored procedure
- Thread starter millerk
- Start date
- Thread starter
- #2
I don't think there's any way to do this using native SQL statements. Any solution will probably need the use of external components, either in the way you describe or by creating a dll and then an extended stored proc in SQL server.
I've never done this myself though so can't really help you with the method.
--James
I've never done this myself though so can't really help you with the method.
--James
- Thread starter
- #4
declare @run_me varchar(1000)
set @run_me = '\\server\path\.exe'
EXEC [server].master..xp_cmdshell @run_me
you can run any command line function within a stored proc using this..
This is a permissions based function meaning you must have permissions to run command lines from the server.
Hope this helps,
Bygs
Anything is possible, it's just how you get there....
set @run_me = '\\server\path\.exe'
EXEC [server].master..xp_cmdshell @run_me
you can run any command line function within a stored proc using this..
This is a permissions based function meaning you must have permissions to run command lines from the server.
Hope this helps,
Bygs
Anything is possible, it's just how you get there....
- Thread starter
- #6
- Thread starter
- #8
Sorry if I wasn't clear enough, I thought it would be assumed that when calling a web service, I would want to get something returned.
I currently have stored procs that use the sp_OA procs to call functions in a COM dll written in VB6. Those functions in my dll use the SAP automation component server object to run SAP transactions. Those SAP transactions return either multiple values or recordsets. Our company will be upgrading SAP to a version which no longer supports access via the SAP Automation Component. So as an alternative, corporate IT is building a web service for each SAP transaction that we use. So we will need to replace our current method of SAP connectivity with calls to these web services.
I know that I will either be able to modify my VB6 COM dll to call these web services, or rewrite the component in vb.net and continue to use the sp_OA procs to access the component.
What I am trying to find out is if there is a way to get rid of the sp_OA calls and instead call the web services directly from our sprocs.
I currently have stored procs that use the sp_OA procs to call functions in a COM dll written in VB6. Those functions in my dll use the SAP automation component server object to run SAP transactions. Those SAP transactions return either multiple values or recordsets. Our company will be upgrading SAP to a version which no longer supports access via the SAP Automation Component. So as an alternative, corporate IT is building a web service for each SAP transaction that we use. So we will need to replace our current method of SAP connectivity with calls to these web services.
I know that I will either be able to modify my VB6 COM dll to call these web services, or rewrite the component in vb.net and continue to use the sp_OA procs to access the component.
What I am trying to find out is if there is a way to get rid of the sp_OA calls and instead call the web services directly from our sprocs.
My God Millerk,
Sounds like who ever makes the decisions in your company needs advice from the Genie (you know the lamp from the IBM commercial)
Maybe someone should re-evaluate the work flow of your current process, because I think you have massive overhead that you dont need.
You can probably accomplish everything within your SQL environment.
If they are going through the trouble to create a web service maybe they should script things to be handled in SQL this way its all in one place and easily maintainable.
Regards,
RU
Sounds like who ever makes the decisions in your company needs advice from the Genie (you know the lamp from the IBM commercial)
Maybe someone should re-evaluate the work flow of your current process, because I think you have massive overhead that you dont need.
You can probably accomplish everything within your SQL environment.
If they are going through the trouble to create a web service maybe they should script things to be handled in SQL this way its all in one place and easily maintainable.
Regards,
RU
- Thread starter
- #10
Let me clarify. The SQL database is local to our plant (about 500 employees) and has been completely designed and implemented by our local dev team(myself included). Corporate IT has no involvement with our local apps or SQL db. And we don't want them to.
The SAP system is located at corporate headquarters, run by corporate IT, and serves the whole company (80,000 employees). SAP is used for inventory, shipping, costing, order planning, etc. Believe me, I'd love to get away from SAP, but that is way, way out of my hands and will not happen. So we are forced to have our system interact with SAP for production reporting and inventory control.
We will have a set of apps for each department that all interact with the SQL db. Two are done and fully operational. The first one does the SAP transactions from the client vb app then stores results in SQL. For the second one, we had decided to make the SAP calls from stored procs instead of from the client. This was to reduce the number of calls back and forth from the client to SQL.
If I had to do it again, I would have done the SAP transactions from the client app because the sp_OA stuff sucks. Especially with the unstable SAP GUI Component Server. But at this point, moving all that logic back to the client would be a 6 month project and we don't have time.
Corporate IT is building the web services to serve the entire company. It is actually a great thing for us because it will simplify the transactions. We'll pass some parameters and get some data back. The current method is basically using UI screen navigation to do the transactions.
So basically I need to convert my VB6 COM object to make web service calls.
It looks like there isn't a way to make the call directly from the procs, so I guess for now I'll be sticking with the VB COM object making the calls.
The SAP system is located at corporate headquarters, run by corporate IT, and serves the whole company (80,000 employees). SAP is used for inventory, shipping, costing, order planning, etc. Believe me, I'd love to get away from SAP, but that is way, way out of my hands and will not happen. So we are forced to have our system interact with SAP for production reporting and inventory control.
We will have a set of apps for each department that all interact with the SQL db. Two are done and fully operational. The first one does the SAP transactions from the client vb app then stores results in SQL. For the second one, we had decided to make the SAP calls from stored procs instead of from the client. This was to reduce the number of calls back and forth from the client to SQL.
If I had to do it again, I would have done the SAP transactions from the client app because the sp_OA stuff sucks. Especially with the unstable SAP GUI Component Server. But at this point, moving all that logic back to the client would be a 6 month project and we don't have time.
Corporate IT is building the web services to serve the entire company. It is actually a great thing for us because it will simplify the transactions. We'll pass some parameters and get some data back. The current method is basically using UI screen navigation to do the transactions.
So basically I need to convert my VB6 COM object to make web service calls.
It looks like there isn't a way to make the call directly from the procs, so I guess for now I'll be sticking with the VB COM object making the calls.
- Status
Similar threads
- Locked
- Question