How to call .net core web api using Sql server?

Question

How to call .net core Web API in sql server?

I tried below then i getting "0x800C0005", "The system cannot locate the resource specified."

DECLARE @Object AS INT ;
DECLARE @hResult INT
DECLARE @Url AS VARCHAR(1000) ;
declare @statusText varchar(1000), @status varchar(1000), @ResponseText AS VARCHAR(8000) ;
SET @Url = 'https://myhost.com/path/to/whatever/'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT ; --
EXEC sp_OAMethod @Object, 'open', NULL, 'get', @Url,'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-type', 'application/json';

EXEC @hResult = sp_OAMethod @Object, 'send';

exec sp_OAGetProperty @Object, 'StatusText', @statusText out
exec sp_OAGetProperty @Object, 'Status', @status out
EXEC sp_OAGetProperty @Object, 'responseText', @ResponseText OUTPUT

is your response more than 4000 characters? https://stackoverflow.com/a/52686957/1910735 — Dawood Awan, Aug 29 '19 at 08:55
your @url doesn't look very URL-y -> it looks to me like `.netCoreAPILink` when I would expect URLs to look more like `http://myhost.com/path/to/whatever/` - if you open a browser on your sqlserver machine, and type `.netCoreAPILink` into the address bar, does it really hit the API and get a valid response? — Caius Jard, Aug 29 '19 at 08:58
no its not more than 4000 characters, and i just needed to call that API, response doesn't matters if i didn't get — nkota, Aug 29 '19 at 08:58
@CaiusJard i update my url and url is hitting from browser address bar and getting valid response. — nkota, Aug 29 '19 at 09:03
@CaiusJard url is hitting from browser and also from sqlserver web browser... and it giving popup for save JSON file — nkota, Aug 29 '19 at 09:12
can you take a screenshot of the browser developer tools window showing the request, the headers etc, and also the network grid, and post it? — Caius Jard, Aug 29 '19 at 09:18
@CaiusJard i am getting response from browser in sqlServer machine — nkota, Aug 29 '19 at 11:20
I wasn't asking you for the screenshot as proof that you were, I was asking you because I want to see the difference between the data your browser is sending and getting a good response versus the data your SP is sending and getting a bad response — Caius Jard, Aug 29 '19 at 11:22
@CaiusJard my mistake was in link i was passing like "www.mysite.com/work" and i removed this www then it worked... thank you so much:) — nkota, Aug 29 '19 at 13:19

score 1 · Answer 1 · answered Aug 29 '19 at 09:09

Try with below sample code

    Declare @Object as Int;
    Declare @ResponseText as Varchar(8000);


    Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
    Exec sp_OAMethod @Object, 'open', NULL, 'get', 'https://myhost.com/path/to/whatever/', 'false'
    Exec sp_OAMethod @Object, 'send'
    Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

    Select @ResponseText

How to call .net core web api using Sql server?

1 Answers1