Calling WS from Sproc

The client wants to update Active Directory from a Sproc via a Web Service.
I found an example in Google Archives, but it won't work...the call to a met
hod
generates the error listed below. Please note that a straight disco call doe
s work.
Wondering if it's a versioning issue.
Thanks for any suggestions...
SQL Server: Microsoft SQL Server 2000 - 8.00.76
MSXML: 6.0
-- WS Code --
<WebMethod()> _
Public Function Hello(ByVal Name As String)
Return "Hello World " & Name
End Function
-- Sproc Code --
CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
AS
DECLARE @.Url varchar(1000)
, @.obj int
, @.hr int
, @.status int
, @.src varchar(4000)
, @.desc varchar(4000)
, @.errmsg varchar(4000)
-- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
-- Generates error in post
SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAMethod @.obj, 'send'
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
exec @.hr = sp_OADestroy @.obj
if @.hr <> 0
begin
EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' description:'
+ @.desc + ' result:' + cast(@.hr as varchar(5))
raiserror(@.errmsg,16,1)
end
-- Error --
[InvalidOperationException: Request format is unrecognized.]
System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(Type t
ype, HttpContext context, HttpRequest request,
HttpResponse response) +388
System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContex
t context, String verb, String url, String filePath)
+94
System.Web.HttpApplication.MapHttpHandler(HttpContext context, String reques
tType, String path, String pathTranslated, Boolean
useAppConfig) +699
System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep
.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& com
pletedSynchronously) +173
</pre></code>Adding the following to the web.config file fixed the problem...
<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>
"Garth Wells" <nobody@.ishome.com> wrote in message news:uXC2hXTYFHA.4024@.TK2MSFTNGP10.phx.g
bl...
> The client wants to update Active Directory from a Sproc via a Web Service
.
> I found an example in Google Archives, but it won't work...the call to a m
ethod
> generates the error listed below. Please note that a straight disco call d
oes work.
> Wondering if it's a versioning issue.
> Thanks for any suggestions...
>
> SQL Server: Microsoft SQL Server 2000 - 8.00.76
> MSXML: 6.0
> -- WS Code --
> <WebMethod()> _
> Public Function Hello(ByVal Name As String)
> Return "Hello World " & Name
> End Function
> -- Sproc Code --
> CREATE PROCEDURE spWSTest (@.Response varchar(8000) out)
> AS
> DECLARE @.Url varchar(1000)
> , @.obj int
> , @.hr int
> , @.status int
> , @.src varchar(4000)
> , @.desc varchar(4000)
> , @.errmsg varchar(4000)
> -- Works SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx'
> -- Generates error in post
> SET @.Url = 'http://localhost/HelloWS/HelloWorld.asmx/Hello?Name=John'
> exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp.6.0', @.obj out
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 1: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.Url, false
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 2: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAMethod @.obj, 'send'
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 3: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OAGetProperty @.obj, 'status', @.status OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 4: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> exec @.hr = sp_OAGetProperty @.obj, 'responseText', @.response OUT
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 5: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
> exec @.hr = sp_OADestroy @.obj
> if @.hr <> 0
> begin
> EXEC sp_OAGetErrorInfo @.obj, @.src OUT, @.desc OUT
> select @.errmsg = 'ERROR 6: HTTPXMLExecute: source:' + @.src + ' descrip
tion:' + @.desc + ' result:' + cast(@.hr as varchar(5))
> raiserror(@.errmsg,16,1)
> end
>
> -- Error --
> [InvalidOperationException: Request format is unrecognized.]
> System.Web.Services.Protocols.WebServiceHandlerFactory.CoreGetHandler(T
ype type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpContext c
ontext, String verb, String url, String
filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String r
equestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutio
nStep.Execute() +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
; completedSynchronously) +173
> </pre></code>
>
>