Re: Assertion: SQL Server 2000 can't issue a SOAP call

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Stefan Delmarco [MSFT] (StefanDe_at_online.microsoft.com)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 09:47:09 -0000

Hi Rob, calling SOAP methods really belong in the application layer (in the
middle tier) and not in SQL Server (Stored Procedures or other). You can do
it but you really need to make sure you want to and understand the
implications. The side effects you will probably see is increased
contention, blocking and / or deadlocking as the most significant portion of
you transaction times will be the responsiveness of the web server hosting
the SOAP endpoint.

To call SOAP methods you'll need to write or use the existing
MSSOAP.SoapClient30 (from the SOAP Toolkit:
http://www.microsoft.com/downloads/details.aspx?FamilyId=C943C0DD-CEEC-4088-9753-86F052EC8450&displaylang=en)
COM component to perform the actual invocation. You'll then need to use the
sp_OA* family of system stored procedures to invoke this COM component.
Note that you have to be a member of sysadmin to call sp_OA SPs. If you
have the SOAP toolkit installed, you can do the following:

DECLARE @object INT
DECLARE @hr INT
DECLARE @src VARCHAR(500)
DECLARE @desc VARCHAR(500)

EXEC @hr = sp_OACreate 'MSSOAP.SoapClient30', @object out, 5
IF @hr <> 0
BEGIN
 EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
 SELECT CONVERT(VARBINARY(4), @HR) AS "HR",
  @src AS "Source",
  @desc AS "Description"
 RETURN
END

DECLARE @bstrWSDLFile VARCHAR(100)
SET @bstrWSDLFile = 'http://www.webservicex.com/StockQuote.asmx?WSDL'
DECLARE @bstrServiceName VARCHAR(100)
SET @bstrServiceName = 'StockQuote'
DECLARE @bstrPort VARCHAR(100)
SET @bstrPort = 'StockQuoteSoap'

EXEC @hr = sp_OAMethod @object, 'mssoapinit', NULL, @bstrWSDLFile,
@bstrServiceName, @bstrPort
IF @hr <> 0
BEGIN
 GOTO CLEANUP
END

DECLARE @StockQuote AS VARCHAR(4000)

EXEC @hr = sp_OAMethod @object, 'GetQuote', @StockQuote OUTPUT, "MSFT"
IF @hr <> 0
BEGIN
 GOTO CLEANUP
END

SELECT @StockQuote AS "StockQuote"
GOTO END_ROUTINE

CLEANUP:

IF @hr <> 0
BEGIN
 EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
 SELECT CONVERT(VARBINARY(4), @HR) AS "HR",
  @src AS "Source",
  @desc AS "Description"
END

EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
 EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
 SELECT CONVERT(VARBINARY(4), @HR) AS "HR",
  @src AS "Source",
  @desc AS "Description"
END

END_ROUTINE:
RETURN 

-- 
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Hindman" <robhindman@hotmail.com> wrote in message
news:d4199cf5.0403222205.211db103@posting.google.com...
> (I originally posted this on comp.databases.ms-sqlserver, but I have
> been asked to re-post to this group... Thanks!)
>
> I really hope I'm wrong about this - I can't find any way to issue a
> SOAP call from SQL Server 2000. I've looked at the BOL, Google, MSDN,
> and the SQLXML documentation and samples...
>
> There is quite a bit of discussion about how to EXPOSE SQL Server 2000
> as a SOAP service, or even exposing stored procedures as web service
> calls.
>
> But I can find absolutely NO mention of calling a separate SOAP
> service from SQL Server 2000, and therefore I would like to assert to
> this group that it can't be done - with the hope of finding out with
> certainty...
>
> For example, I have the following wsdl target:
> http://mssoapinterop.org/stk/Interop.wsdl, and I would like to call
> the Interop/SoapPort/echoString method. (It takes any string for the
> inputString parameter, and just echoes it back.)
>
> I'd love to do this from a stored procedure, or from an extended
> stored procedure, but I don't think that it can be done AT ALL - which
> makes me curious - if SQL Server 2000 with SQLXML can expose
> functionality as SOAP methods, surely it would be possible to issue a
> SOAP call as well?
>
> Many Thanks to anyone who can confim this assertion or show it to be
> false.
> -Rob.
>
> PS - Also, I have called MSDN support, and I will let both groups know
> if there is a solution or work-around.
> -R.

Relevant Pages

  • Re: Assertion: SQL Server 2000 cant issue a SOAP call
    ... > middle tier) and not in SQL Server. ... > the SOAP endpoint. ... > sp_OA* family of system stored procedures to invoke this COM component. ... > DECLARE @object INT ...
    (microsoft.public.sqlserver.programming)
  • Re: using url access to native xml web services like SQLXML
    ... > to expose Stored Procedures via SOAP. ... You need to be running SQL Server ... > of the box without having to maintain any SQLXML IIS vdirs. ... >>anything in the documentation for native xml web services. ...
    (microsoft.public.sqlserver.xml)
  • Assertion: SQL Server 2000 cant issue a SOAP call
    ... SOAP call from SQL Server 2000. ... the Interop/SoapPort/echoString method. ... I'd love to do this from a stored procedure, ...
    (microsoft.public.sqlserver.programming)
  • Re: Assertion: SQL Server 2000 cant issue a SOAP call
    ... It might be worth mentioning that calling CLR code from SQL Server is not ... > from a stored procedure, or from an extended stored procedure, but I don't ... >> SOAP call from SQL Server 2000. ... >> PS - Also, I have called MSDN support, and I will let both groups know ...
    (microsoft.public.sqlserver.programming)
  • RE: Textbox and binding DbNull Values
    ... If you are using stored procedures (sql server) you could do like this ... Declare @closed_date_time datetime ...
    (microsoft.public.dotnet.general)