Ole Automation Procedure returns null

Question

I have the following issue. running the sql below on our server it returns the expected results. Running the same on another server it returns no values.

Did the following:

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

USE tempdb
GO

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://exampleURL' -- + @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


declare @input XML=(
SELECT
yourXML
from
#xml)

SELECT
Item.value('(Code)[1]', 'nvarchar(max)') as Code,
Item.value('(Description)[1]', 'varchar(max)') as Description,
Item.value('(ImageUrl)[1]', 'nvarchar(max)') as ImageUrl
from
@input.nodes('//product') AS T(Item)

Within the second server the @input returns null. There is a proxy to access the site on the server and it operates with sql server 2008.

Any ideas why the null values?

The lack of convenience in troubleshooting this stuff is a huge incentive to move towards client code for this instead of having SQL Server do direct HTTP downloads, IMO. (Aside from the other issues, like resource consumption and stability -- please take care to clean up your objects with sp_OADestroy when you're done, even if this code is just for illustration.) — Jeroen Mostert

Weihui Guo Weihui Guo · Accepted Answer · 2019-05-10T15:13:43

I just had a similar issue, a query using Ole Automation suddenly returns null without any error. After changing 'MSXML2.XMLHttp' to 'MSXML2.ServerXMLHTTP', it started to work again.

To know more about the difference between these two, see this article and Microsoft documentation. I copied some from Microsoft site, in case both sites are down in the future.

The ServerXMLHTTP object offers functionality similar to that of the XMLHTTP object. Unlike XMLHTTP, however, the ServerXMLHTTP object does not rely on the WinInet control for HTTP access to remote XML documents. ServerXMLHTTP uses a new HTTP client stack. Designed for server applications, this server-safe subset of WinInet offers the following advantages:

Reliability — The HTTP client stack offers longer uptimes. WinInet features that are not critical for server applications, such as URL caching, auto-discovery of proxy servers, HTTP/1.1 chunking, offline support, and support for Gopher and FTP protocols are not included in the new HTTP subset.

Security — The HTTP client stack does not allow a user-specific state to be shared with another user's session. ServerXMLHTTP provides support for client certificates.

Ole Automation Procedure returns null

2 Answers