sp_OAGetProperty always returns a NULL

Question

Post reply

sp_OAGetProperty always returns a NULL

dndaughtery

SSChampion

Points: 11286
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

Viewing 6 posts - 1 through 5 (of 5 total)

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

dndaughtery SSChampion Points: 11286 More actions · Answer 1

dndaughtery

SSChampion

Points: 11286

March 11, 2019 at 12:59 pm

#2024129

I am using SQL 2012

Scott Coleman One Orange Chip Points: 27451 More actions · Answer 2

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: 11286 More actions · Answer 3

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 · Answer 4

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: 11286 More actions · Answer 5

dndaughtery

SSChampion

Points: 11286

March 11, 2019 at 1:21 pm

#2024137

Probaby not. Thanks