locked
sp_OAGetProperty 'responseXml' limits RRS feed

  • Question

  • When running the following SQL we have found an error that always occurs on the 253rd time that we call sp_OAGetProperty... 'responseXml'.... Please advise a way in which we can receive an xml response from a specified URL without hitting this limitation. Also is there a way to receive the XML response as a different output variable besides VARCHAR(8000). We will be receiving xml that can get much larger than 8000. Thanks.

     

    DECLARE 
    @objFileSystem INT,
    @objTextStream INT,
    @objXmlHttp INT, 
    @objDOMDocument INT,
    @hResult INT, 
    @RequestBody VARCHAR(MAX), 
    @MethodName VARCHAR(50), 
    @UserName nVARCHAR(100), 
    @Password nVARCHAR(100),
    @FileAndPath VARCHAR(355),
    @YesOrNo BIT,
    @Chunk VARCHAR(8000),
    @ParmDefinition NVARCHAR(100),
    @SQLString NVARCHAR(MAX),
    @ResponseText VARCHAR(8000),
    @ErrorSource VARCHAR(255),
    @ErrorDesc VARCHAR(255),
    @ErrorFailPoint VARCHAR(50),
    @Path VARCHAR(255),
    @Filename VARCHAR(100),
    @URI VARCHAR(255)
    	
    	SET @URI = 'http://localhost/'
    	SET @ResponseText = 'FAILED' 
    	SET @MethodName = 'POST'
    	SET @FileAndPath = 'C:\test.txt'
    	SET @UserName = ''
    	SET @Password = ''
    			
    DECLARE @Record INT
    SET @Record = 0
    
    
    WHILE @Record < 260
    BEGIN
    	SET @Record = @Record + 1
    	
    	PRINT @Record
    	
    	EXECUTE @hResult = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objFileSystem, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Creating FSO'
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	EXECUTE @hResult = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FileAndPath, 1, false, 0--for reading, FormatASCII
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objFileSystem, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Opening Request File'
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	SET @RequestBody = ''
    	WHILE @hResult = 0
    	BEGIN
    		EXECUTE @hResult = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT
    		IF @hResult <> 0 
    		BEGIN 
    			EXEC sp_OAGetErrorInfo @objTextStream, @ErrorSource OUT, @ErrorDesc OUT 
    			SET @ErrorFailPoint = 'Checking AtEndOfStream'
    			GOTO Destroy 
    			RETURN 
    		END 	
    		
    		IF @YesOrNo <> 0
    			BREAK
    			
    		EXECUTE @hResult = sp_OAMethod  @objTextStream, 'Read', @Chunk OUTPUT, 4000
    		IF @hResult <> 0 
    		BEGIN 
    			EXEC sp_OAGetErrorInfo @objTextStream, @ErrorSource OUT, @ErrorDesc OUT 
    			SET @ErrorFailPoint = 'Reading Chunk'
    			GOTO Destroy 
    			RETURN 
    		END 	
    		SET @RequestBody = @RequestBody + ISNULL(@Chunk, '')
    	END
    	SET @RequestBody = 'xml=' + REPLACE(@RequestBody, ' ', '+')
    	
    	EXECUTE @hResult = sp_OAMethod  @objTextStream, 'Close'
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objTextStream, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Closing Request File'
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objXmlHttp OUT 
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objXmlHttp, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Creating MSXML2.ServerXMLHTTP' 
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	-- open the destination URI with Specified method 
    	EXEC @hResult = sp_OAMethod @objXmlHttp, 'open', NULL, @MethodName, @URI, 'false', @UserName, @Password 
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objXmlHttp, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Opening URI' 
    		GOTO Destroy 
    	END
    	
    	-- SET request headers 
    	EXEC @hResult = sp_OAMethod @objXmlHttp, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' --'text/xml;charset=ASCII' 
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objXmlHttp, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Setting RequestHeader Content-Type'
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	DECLARE @Len INT 
    	SET @Len = LEN(@RequestBody) 
    	EXEC @hResult = sp_OAMethod @objXmlHttp, 'setRequestHeader', NULL, 'Content-Length', @Len 
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objXmlHttp, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Setting RequestHeader Content-Length'
    		GOTO Destroy 
    		RETURN 
    	END 
    	
    	--SEND
    	SET @ParmDefinition = N'@x_objXmlHttp INT'	
    	SET @SQLString = N'EXEC sp_OAMethod @x_objXmlHttp, ''send'', NULL, ''' + REPLACE(@RequestBody, '''', '''''') + ''''	
    	EXEC @hResult = sp_ExecuteSQL @SQLString, @ParmDefinition, @x_objXmlHttp = @objXmlHttp
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objXmlHttp, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint ='Sending Request'
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	EXEC @hResult = sp_OACreate 'Msxml2.DOMDocument', @objDOMDocument OUT
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objDOMDocument, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Creating Msxml2.DOMDocument'
    		GOTO Destroy 
    		RETURN 
    	END 
    
    	-- Get response
    	EXEC @hResult = sp_OAGetProperty @objXmlHttp, 'responseXml', @objDOMDocument OUT
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo NULL, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Retrieving Response'
    		GOTO Destroy 
    		RETURN 
    	END 
    	
    	EXEC @hResult = sp_OAMethod @objDOMDocument, 'xml', @ResponseText OUT
    	IF @hResult <> 0 
    	BEGIN 
    		EXEC sp_OAGetErrorInfo @objDOMDocument, @ErrorSource OUT, @ErrorDesc OUT 
    		SET @ErrorFailPoint = 'Packaging Response' 
    		GOTO Destroy 
    		RETURN 
    	END 
    
    		Destroy: 
    		  IF @hResult <> 0
    		  BEGIN
    		      SELECT
    			    @Record AS RecordNumber,
    		    	@ErrorFailPoint AS ErrorFailPoint,
     	     	  	@ErrorSource AS ErrorSource, 
         		  	@ErrorDesc AS ErrorDesc
    		  END
    		  
    		  EXEC sp_OADestroy @objXmlHttp 
    		  EXEC sp_OADestroy @objFileSystem
    		  EXEC sp_OADestroy @objTextStream
    		  EXEC sp_OADestroy @objDOMDocument
    END
    

     

    Friday, November 4, 2011 12:57 AM

All replies

  • The SSIS Web Service task might be better for this:

    Web Service Task
    http://msdn.microsoft.com/en-us/library/ms140114.aspx

    Friday, November 4, 2011 9:44 AM
    Answerer
  • Hi wBob,

    Thanks for the suggestion but SSIS is not an option at this time. We are still limited to only processing 250 records at a time and responses that are less than 8000 characters because of this limitation.

    Also we found it interesting that a memory leak was supposively fixed a while back for the sp_OAGetProperty when an output object is returned.


    • Edited by JT37 Friday, November 4, 2011 6:10 PM
    Friday, November 4, 2011 4:50 PM