December 30, 2008 at 7:07 am
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
August 12, 2021 at 8:02 pm
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
Sandeep Kataria
August 15, 2021 at 10:52 pm
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.
August 15, 2021 at 11:15 pm
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.
August 16, 2021 at 3:30 pm
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
Sandeep Kataria
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply