WinHttp.WinHttpRequest

• DB Dude

  Old Hand

  Points: 364

  More actions

  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!!

• francesco.mantovani

  SSC Eights!

  Points: 840

  More actions

  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