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? stackoverflow.com/a/52686957/1910735 — Dawood Awan
– Dawood Awan, Commented Aug 29, 2019 at 8: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
– Caius Jard, Commented Aug 29, 2019 at 8: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
– nkota, Commented Aug 29, 2019 at 8:58
@CaiusJard i update my url and url is hitting from browser address bar and getting valid response. — nkota
– nkota, Commented Aug 29, 2019 at 9:03
@CaiusJard url is hitting from browser and also from sqlserver web browser... and it giving popup for save JSON file — nkota
– nkota, Commented Aug 29, 2019 at 9:12

Md. Touhidul Alam · Accepted Answer · 2019-08-29 09:09:26Z

1

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

answered Aug 29, 2019 at 9:09

Md. Touhidul Alam

264 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related