Facing Problem When Calling Web API from SQL Function RRS feed

  • Question

  • Hello Team,

    I am calling API of web service inside of SQL Function. Currently I have created stored proc which contains logic mentioned below. I have to execute this logic from SQL view, but from view we can not call stored proc. So decided to convert this running stored proc to SQL function.

    In given below created SQL function, we are getting response Text which is inserted to variable (highlighted in bold). This logic is perfectly working fine in stored proc, but not able to execute INSERT inside SQL function. Can you please guide me in this case. 

    Note -  I have tried OpenQuery() concept also, but didn't help much.

    SQL Function -

    CREATE FUNCTION [Default].[GetImageData]

           @document_ID UNIQUEIDENTIFIER
    RETURNS nvarchar(MAX)

            Declare @Object as Int;
            DEClare @docStorageUrl nvarchar(100);
            DEClare @idparam nvarchar(100);
           DEClare @url nvarchar(max);
           declare @requestText nvarchar(max);
          DECLARE @status NVARCHAR(32);
      declare @res int;
      DECLARE @ret INT;
      DECLARE @statusText NVARCHAR(32);
      DECLARE @source varchar(255), @desc varchar(255)
      DECLARE @responseText as table(responseText nvarchar(max))
      DECLARE @responseXMLText as nvarchar(MAX)
      DECLARE @responseXMLText2 as nvarchar(MAX)
      DECLARE @Response VARCHAR(MAX)
      DECLARE @responseXMLText1 VARCHAR(MAX)
      DECLARE @sql VARCHAR(8000)
      DECLARE @cmd VARCHAR(8000)

     set @idparam = CONVERT(nvarchar(100), @document_ID)


    SET @requestText = '<s:Envelope xmlns:s=""><s:Body><GetDocumentDataByDocumentID xmlns=""><documentId>abcdf3e67-af00-xa11-a8102-005056a2e7eb</documentId></GetDocumentDataByDocumentID></s:Body></s:Envelope>' -- include the id in the correct bit(see fiddler)

        set @url= ''

        Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
        Exec sp_OASetProperty @Object, 'NtlmAuth', 1;
        Exec @ret = sp_OAMethod @Object, 'open', NULL, 'POST', @url, 'false', 'ValidUser', 'Test1';
        Exec @ret = sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'text/xml; charset=utf-8';
        Exec @ret = sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Length', 242;
        Exec @ret = sp_OAMethod @Object, 'setRequestHeader', NULL, 'SOAPAction',  ''; 
        Exec @res = sp_OAMethod @Object, 'send', NULL, @requestText;

      -- Handle response
     EXEC @ret = sp_OAGetProperty @Object, 'status', @status OUT;
     EXEC sp_OAGetErrorInfo @Object, @source OUT, @desc OUT
     EXEC @ret = sp_OAGetProperty @Object, 'statusText', @statusText OUT;

     INSERT INTO @ResponseText (ResponseText) EXEC sp_OAGetProperty @Object, 'responseText'

     Select @responseXMLText = ResponseText from @ResponseText

    SET @responseXMLText1 = CONVERT(nvarchar(max), CAST( @responseXMLText AS XML).query('declare namespace s=""; declare namespace t=""; /s:Envelope/s:Body/t:GetDocumentDataByDocumentIDResponse/t:GetDocumentDataByDocumentIDResult/text()'))

      RETURN @responseXMLText1;


    Sudarshan Gujar

    • Edited by Sud Gujar Thursday, November 7, 2019 7:51 PM
    Thursday, November 7, 2019 7:46 PM

All replies