MSXML2.XMLHTTP

  • Using sp_OACreate(MSXML2.XMLHTTP)

    Object creates. Open("POST"....) works. Send() never returns webpage.

    I've tried GET and POST to various websites. No answer ever returns.

    -->Here's the code that "should" work...

    declare @inp char(80),@out varchar(300)

    set nocount on

    set @inp = 'some input request string '

    Declare @userid varchar(15),@Password varchar(15)

    set @userid = 'some userid'

    set @Password = 'some password'

    set @out = '???'

    declare @Object int,@hr int,@URL varchar(80),@i int

    set @URL = 'http://www.microsoft.com'

    declare @NULL Binary

    set @NULL = null

    exec @hr= sp_OACreate 'MSXML2.XMLHTTP.6.0', @object OUT

    IF @hr <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    RETURN

    END

    exec sp_OAMethod @object,'open',@hr out,'GET',@url,FALSE,@Userid,@Password

    IF @hr <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    RETURN

    END

    EXEC sp_OAMethod @object, 'setRequestHeader',@hr out,'Content-Length','50'

    if @hr != 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    Return

    end

    EXEC sp_OAMethod @object,'setRequestHeader',@hr out,'Content-type','application/x-www-form-urlencoded'

    if @hr != 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    Return

    end

    EXEC sp_OAMethod @object,'send',@hr out,@inp

    if @hr != 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    Return

    end

    set @i = 300

    EXEC sp_OAGetProperty @object,'readystatechange',@hr OUT

    -- Wait for response up to 30 seconds

    while @hr != 4 and @i > 0

    begin

    EXEC sp_OAGetProperty @object,'readystatechange',@hr OUT

    set @i = @i - 1

    end

    EXEC @hr= sp_OAGetProperty @object,'responseText',@out out

    if @hr = 0 select @out

    --if @hr = 0 EXEC @hr= sp_OAGetProperty @object,'responseText',@out OUT

    IF @hr <> 0

    BEGIN

    EXEC sp_OAGetErrorInfo @object

    select 'Get Property',@hr

    END

    exec sp_OAdestroy @object

  • Can someone help.... i am getting a null response. if you pass the actual URL u do get a JSON back. Waht am i doing so wrong.....the HTTP gods are going against me!

    Declare @Object as Int;

    Declare @ResponseText as Varchar(8000);

    Declare @Body as varchar(8000) =

    '{

    "Subsystem": 1,

    "Exception": "",

    "Message": "I have done what you asked",

    "Time": "2014-06-09T11:16:35",

    "Attribute": { "Number of attempts": "0" }

    }'

    Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;

    EXEC sp_OAMethod @Object, 'open', NULL, 'get','https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=32277993', 'false'

    Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'

    Exec sp_OAMethod @Object, 'send', null, @body

    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    Select @ResponseText

    Exec sp_OADestroy @Object


    Kind Regards,

    Sandeep Kataria

  • Is the SQL server you're using allowed a direct outbound connection to the internet?

    If you're using a proxy, then this must be set up in the internet settings on the server. Bear in mind that this is set up per user.

    AFAIK, MSXML2.XMLHTTP doesn't allow you to specify proxy settings. It will always use the user's internet settings.

  • Okay, I was wrong. It does seem to support proxy setup:

    https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms760236(v=vs.85)

    https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms763680(v=vs.85)

    So you might want to fool around with those parameters, if you need to utilize a proxy in order to get to the internet from the SQL server.

     

  • Hello kaj

    Appreciate your response and pointing me to resources. The below code worked for me. apparently the declaration of version mattered. i commented out errors trap but they can easily be put back in to caught in the procedure.

     

    Declare @Object as Int;

    DECLARE @hr int

    Declare @json as table(Json_Table nvarchar(max))

    Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;

    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',

    'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=32277993', --Your Web Service Url (invoked)

    'false'

    --IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    Exec @hr=sp_OAMethod @Object, 'send'

    --IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT

    --IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

    INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

    -- select the JSON string

    select * from @json

    EXEC sp_OADestroy @Object

     


    Kind Regards,

    Sandeep Kataria

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

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