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>There are a couple different ways to do this in SQL Server 2005 but there
aren't ant really good ways to do this in SQL Server 2000. Several people
have used external com objects called through the sp_OA* stored procedures
but that's not a very scaleable solution. I would suggest writing the
information you want to add to AD into a table and then writing an
application that occasionally reads the table and does the AD update. AD
updates are somewhat asynchronous anyway so the delay is probably tolerable.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Garth Wells" <nobody@.ishome.com> wrote in message
news:e5ln2oeYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> 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
> method
> generates the error listed below. Please note that a straight disco call
> does 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
> type, HttpContext context, HttpRequest request,
> HttpResponse response) +388
> System.Web.Services.Protocols.WebServiceHandlerFactory.GetHandler(HttpCont
ext
> context, String verb, String url, String filePath)
> +94
> System.Web.HttpApplication.MapHttpHandler(HttpContext context, String
> requestType, String path, String pathTranslated, Boolean
> useAppConfig) +699
> System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionSt
ep.Execute()
> +95
> System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&
> completedSynchronously) +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:e5ln2oeYFHA.3320@.TK2MSFTNGP12.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>
>