Well, with input from a few people in a forum and my own investigation, I've found only three ways to call a web service from an MS SQL Server 2000 system. Here are the three ways:
- Write an exe and call it from xp_cmdshell (ugh)
- Write an extended stored procedure and do it in there (better)
- Do a vbscript call from a Scheduled Job (ok, but not ideal)
I ended up going with #3. The script is
I really hope that with the upcoming releases of Whidbey and Yukon, Microsoft will make advances in linking to web services/web references. MS makes it fairly easy to link to an ODBC datasource in linked servers, this would just seem like the next logical step.
4 comments:
I am attempting #3.
Below code no worky worky ... broke dick... need big fix.
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
dim sResponse
'Call Functoin that calls web service which returns county XML for each state.
sResponse = Call_NET_WS("http://blahblahblah.com/cool_WS/cool_WS.asmx?WSDL", DTSSource("State"))
DTSDestination("sXML") = sResponse
Main = DTSTransformStat_OK
End Function
Function Call_NET_WS(URL, sState)
Dim objSoapClient
Dim strResponse
Set objSoapClient = CreateObject("MSSOAP.SoapClient30")
objSoapClient.MSSoapInit Url
strResponse = objSoapClient.ReturnCounties(sState)
Call_NET_WS = strResponse
End Function
Only anon cuz don't belong.
Just posted.
Getting
"Wrong number of arguments or invalid property assignment" on line
"strResponse = objSoapClient.ReturnCounties(sState)"
WS works fine when calling from browser.
Only anon cuz don't belong
OK, here's the deal. The .Net Web Service that the DTS package will consume must return a string or other simple data type that is not serialized.
I had the creator of the WS overload the method so it returned a string instead of a DataSet. The code below works. I will later have to parse out the XML find exactly what I am looking for. This XML can be loaded into a DOM doc or you can just parse out with string functions. Let me know if this helps.
'********************
Function Main()
dim sResponse
'Call Functoin that calls web service
sResponse = Call_NET_WS("http://Blah/Blah_WS/Blah_WS.asmx?WSDL", DTSSource("State"))
DTSDestination("sXML") = sResponse
Main = DTSTransformStat_OK
End Function
Function Call_NET_WS(URL, sState)
Dim objSoapClient, strResponse
Set objSoapClient = CreateObject("MSSOAP.SoapClient30")
objSoapClient.MSSoapInit Url
strResponse = objSoapClient.ReturnCountiesXML(sState)
Call_NET_WS = strResponse
End Function
Only anon cuz don't belong
my God, i thought you were going to chip in with some decisive insght at the end there, not leave it with ‘we leave it to you to decide’.
rH3uYcBX
Post a Comment