Calling WS from Sproc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calling WS from Sproc

Author

Topic

Garth
SQLTeam Author

119 Posts

Posted - 2005-05-25 : 11:59:56

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(HttpContext 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+IExecutionStep.Execute() +95
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +173 </pre></code>

Garth
SQLTeam Author

119 Posts

Posted - 2005-05-27 : 08:42:53

Adding the following to the web.config file fixed the problem...

<webServices>
<protocols>
<add name="HttpGet" />
</protocols>
</webServices>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources