Calling a web service in SQL2K

External Since: Mar 13, 2007 Posts: 2

I've isntalled the SOAP Toolkit on a SQL Server 2000 box (see:
http://tinyurl.com/aw2o) and am trying to invoke a web service from a stored
procedure.

I have been successful in retrieving simple datatype values (from calls to
GetInteger and GetString) but when the returned value is not simple (call to
GetComplex) what I get is a handle I don't know what to do with... I've tried
retrieving properties on the returned object and have read through both the
SoapClient30 documentation and the OLE Automation stored procedures docs but
can't quite seem to figure it out.

Below is my web service, with the XML returned in two relevant cases, and a
working example of a stored procedure that retrieves a string value... can
anyone help?

--- Web Service
---------------------------------------------------------------

using System;
using System.Web.Services;

[WebService(Namespace="http://tcw.com/")]

public class SecurityWebService : WebService
{
[WebMethod(Description="Returns a string",EnableSession=false)]
public string GetString()
{
return "Gewurtztramminer";
}

[WebMethod(Description="Returns a given string",EnableSession=false)]
public string GetStringWithArg(string s)
{
if (s == "") s = "Gewurtztramminer";
return s;
}
}

--- Returned XML for String method call -------------------------------------

<?xml version="1.0" encoding="utf-8" ?>
<string xmlns="http://tcw.com/">Gewurtztramminer</string>

--- Returned XML for Complex method call ----------------------------------

<?xml version="1.0" encoding="utf-8" ?>
- <ComplexReturn xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tcw.com/">
<Code>Test</Code>
<Price>3</Price>
</ComplexReturn>

--- Stored Procedure
----------------------------------------------------------

if object_id('ecx') is not null
drop proc ecx
go
CREATE PROC ecx
AS
DECLARE @err int
, @sh int -- SOAP handle
, @ret varchar(8000)
, @msg varchar(32)

SELECT @msg = 'sp_OACreate'
EXEC @err = sp_OACreate 'MSSOAP.SoapClient30', @sh OUT

IF @err = 0
BEGIN
SELECT @msg = 'MSSoapInit'
EXEC @err = sp_OAMethod @sh
, 'MSSoapInit'
, NULL
, 'http://LAD1009291/SampleService.asmx?WSDL'
END

IF @err = 0
BEGIN
SELECT @msg = 'GetComplex'
EXEC @err = sp_OAMethod @sh
, 'GetString'
, @ret OUT
END

IF @err = 0
BEGIN
SELECT @msg = 'sp_OADestroy'
EXEC sp_OADestroy @sh
END

DECLARE @src varchar(8000)
, @desc varchar(8000)

IF @err = 0
SELECT [ret] = @ret
ELSE
BEGIN
EXEC sp_OAGetErrorInfo @sh, @src OUT, @desc OUT
PRINT @msg
PRINT @src
PRINT @desc
PRINT convert(varbinary(4), @err)
END
go

EXEC ecx

External Since: Feb 03, 2008 Posts: 219

Here is an example of a call to a web service using SOAP Toolkit
within SQL Server:

DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'MSSOAP.SoapClient30', @object OUT
IF @hr <> 0 GOTO errorhandler

EXEC @hr = sp_OAMethod @object, 'MSSoapInit', NULL, 'http://
ec.europa.eu/taxation_customs/vies/api/checkVatPort?wsdl'
IF @hr <> 0 GOTO errorhandler

DECLARE @Name varchar(1000)

EXEC @hr = sp_OAMethod @object, 'checkVat', NULL, 'RO', 19, NULL,
NULL, @Name OUT, NULL
IF @hr <> 0 GOTO errorhandler

PRINT @name

GOTO finish

errorhandler:
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc

finish:
EXEC @hr = sp_OADestroy @object

The above code should print "BUCUR OBOR SA", which is the name of the
company having the VAT code "RO19" in Romania.

However, be advised that SOAP Toolkit is no longer supported (since
April 2005) and that using the sp_OA procedures is usually tricky.
Therefore you should consider upgrading to SQL Server 2005, where you
can create a CLR stored procedure and invoke the web service using the
standard .NET calls.

Razvan