CODEWITHSUNDEEP
sql-servert-sqlole-automation
p4n1
p4n1

Reputation: 115

Ole Automation Procedure returns null

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?

Upvotes: 0

Views: 1187

Answers (3)

Johan van de Pol
Johan van de Pol

Reputation: 1

We had the issue as well, that is was very unstable. After root cause analysis compared to our other environments it was memory related. After adding memory to the server, all issues were solved.

Upvotes: -1

Weihui Guo
Weihui Guo

Reputation: 3997

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.

Upvotes: 1

p4n1
p4n1

Reputation: 115

So, it seems that my issue was that I had no access over the internet from my SQL server, so I had to use the below line to set the proxy.

exec @Result = sp_OAMethod @Obj, 'setProxy', NULL, '2', 'http://myProxy'

Once this was sorted, I managed to get my results.

Upvotes: -1

Related Questions