Post reply

WinHttp.WinHttpRequest

  • December 22, 2011 at 10:26 am

    #252396

    Hello,

    I have a stored proc that calls a web service via WinHttp.WinHttpRequest.5.1. For years, I had this code working without any problems. Suddenly, it will not work. I found that when creating the object, it returns a null output (@http is null and @rc is negative number). CLR enabled, Xpcmdshelel enabled, OLEAutomation enabled. Security to sp_OA procs is not an issue. No changes to the server that I am aware of. Also, this server is SQL2005 Enterprise (9.0.4035) in a cluster on WinServer 2003.

    --code snippet

    DECLARE

    @http int,

    @rc int

    EXEC @rc = master.dbo.sp_OACreate 'WinHttp.WinHttpRequest.5.1', @http out

    SELECT @rc, @http

    EXEC @rc = master.dbo.sp_OADestroy @http

    Again, @http is NULL and @rc = -2147023782

    Any ideas would be greatly appreciated!!

  • October 7, 2018 at 4:48 pm

    #2008481

    Do this: 

    Declare @Object as Int;
    DECLARE @hr int
    Declare @json as table(Json_Table nvarchar(max))

    Exec @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
          'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
          'false'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'send'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
    Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
    -- select the JSON string
    select * from @json
    -- Parse the JSON string
    SELECT * FROM OPENJSON((select * from @json), N'$.elements')
    WITH ( 
      [type] nvarchar(max) N'$.type' ,
      [id] nvarchar(max) N'$.id',
      [lat] nvarchar(max) N'$.lat',
      [lon] nvarchar(max) N'$.lon',
      [amenity] nvarchar(max) N'$.tags.amenity',
      [name] nvarchar(max) N'$.tags.name' 
    )
    EXEC sp_OADestroy @Object

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply