sp_OAGetProperty always returns a NULL

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 11, 2019 at 12:49 pm

  #409683

  I found the following code online and was trying to run it but I am not getting back the XML. I keep getting back NULL. Can someone see what Im doing wrong?

  I have already ran the following:

  sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO

  Heres the code I keep getting a null

  IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml

  CREATE TABLE #xml ( yourXML XML )

  GO

  DECLARE @URL VARCHAR(8000)

  DECLARE @QS varchar(50)

  -- & or ? depending if there are other query strings

  -- Use this for when there is other query strings:

  SELECT @QS = '&date='+convert(varchar(25),getdate(),126)

  -- Use this for when there is NO other query strings:

  -- SELECT @QS = '?date='+convert(varchar(25),getdate(),126)

  SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false' + @QS

  DECLARE @Response varchar(8000)

  DECLARE @XML xml

  DECLARE @Obj int

  DECLARE @Result int

  DECLARE @HTTPStatus int

  DECLARE @ErrorMsg varchar(MAX)

  EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

  EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false

  EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

  EXEC @Result = sp_OAMethod @Obj, send, NULL, ''

  EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

  INSERT #xml ( yourXML )

  EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT

  SELECT yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 11, 2019 at 12:59 pm

  #2024129

  I am using SQL 2012

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  March 11, 2019 at 1:10 pm

  #2024131

  You need to clarify whether the "INSERT #xml EXEC sp_OAGetProperty" is returning NULL, or whether the table is indeed getting an XML value but "yourXML.value()" results in NULL.

  If you're getting valid XML but your XPATH query is wrong, you would need to post the XML (or a sample) for anyone to have a suggestion.

  If you're not getting a response, you should find out what the error message is.  As shown in this example:
  EXEC @hr = sp_OAGetProperty @object, 'HostName';
IF @hr <> 0 BEGIN
   EXEC sp_OAGetErrorInfo @object
  RETURN
END;

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 11, 2019 at 1:14 pm

  #2024133

  When I paste the url into IE I get the following XML displayed in the browser:

  <?xml version="1.0" encoding="UTF-8"?>

  -<GeocodeResponse>

  <status>REQUEST_DENIED</status>

  <error_message>You must use an API key to authenticate each request to Google Maps Platform APIs. For additional information, please refer to http://g.co/dev/maps-no-account</error_message>

  </GeocodeResponse>

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 11, 2019 at 1:20 pm

  #2024136

  dndaughtery - Monday, March 11, 2019 12:49 PM

  I found the following code online and was trying to run it but I am not getting back the XML. I keep getting back NULL. Can someone see what Im doing wrong?

  I have already ran the following:

  sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO

  Heres the code I keep getting a null

  IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml

  CREATE TABLE #xml ( yourXML XML )

  GO

  DECLARE @URL VARCHAR(8000)

  DECLARE @QS varchar(50)

  -- & or ? depending if there are other query strings

  -- Use this for when there is other query strings:

  SELECT @QS = '&date='+convert(varchar(25),getdate(),126)

  -- Use this for when there is NO other query strings:

  -- SELECT @QS = '?date='+convert(varchar(25),getdate(),126)

  SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?latlng=10.247087,-65.598409&sensor=false' + @QS

  DECLARE @Response varchar(8000)

  DECLARE @XML xml

  DECLARE @Obj int

  DECLARE @Result int

  DECLARE @HTTPStatus int

  DECLARE @ErrorMsg varchar(MAX)

  EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

  EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false

  EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

  EXEC @Result = sp_OAMethod @Obj, send, NULL, ''

  EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

  INSERT #xml ( yourXML )

  EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT

  SELECT yourXML.value('(//GeocodeResponse/status)[1]','VARCHAR(MAX)') from #xml

  Does your firewall allow the outbound http traffic for SSMS?
  Worked for me when I allowed that in the firewall. 

  Sue

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  March 11, 2019 at 1:21 pm

  #2024137

  Probaby not. Thanks