May 1, 2007 at 10:49 am
Any ideas on this?
I need to be able to call a web service from with a stored procedure, and it we need to be able to run this in a SQL Server 2000 environment. I know it would be much easier in 2005 with .NET, but it looks almost impossible in 2000.
I've seen the SQLXLML stuff, but most of it talks about exposing SQL Server rather than using it to consume a web service.
Basically I need to have a stored procedure that can either call the web service. Or it is feasible to build an extended stored procedure that calls a .NET object to communicate with the web service?
May 1, 2007 at 11:13 am
On SQL2000 you need to avoid XML.
It uses MSXML3 where as the most recent version is MSXML6
The only way that I can think of to do what you are expecting to do is to create a COM object, register it on your SQL Server then use the sp_OACreate etc procedures to call its methods.
This is not something that I would do in a production environment.
May 2, 2007 at 10:57 am
What would be the issues of implementing it via COM/sp_OACreate?
May 2, 2007 at 12:10 pm
You have to register your COM component on your database server and not many DBAs are keen on this.
You need to be sure that your COM component is well written as you don't want memory leaks on your database server.
The users who you want to call sp_OACreate etc will need permissions to do so.
I have had some success with having a separate server making a call to a web service, assembling it into a file and then calling a SQL Server BULK INSERT command, but it still seemed a bit clunky.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply