Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I want to be able to POST a json message to a REST service. I am able to do a GET call by using examples from google-search

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT', 
    --Your Web Service Url (invoked)    'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText
Exec sp_OADestroy @Object
I have tried different variations of the following:
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255) 
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.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post', 
    'https://thecorrecturl:446/api/handelse/', 'false'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
declare @len int
set @len = len(@body)
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
Exec sp_OAMethod @Object, 'setRequestBody', null, 'Body', @body
EXEC sp_OAMethod @Object, 'send', null
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText
Exec sp_OADestroy @Object
The ResponseText I get back is the omnious "{"Message":"An error has occurred."}"
Can anyone point me in the right direction to do this or just give the correct way to do it ;)
I have used the GET call to my url and got the correct handelse from the service.
Regards
Caroline
                Did you notice that false all the way on the right? after the "your web service URL" comment. does that trigger the issue?
– Ruskin
                Jun 9, 2014 at 11:01
                If I change from false to true I get a NULL response instead but still the same error on the server side (from the IIS log POST /api/handelse/ - 446 - XXXXXXXXXXX 500 0 0 203)
– user3721971
                Jun 9, 2014 at 11:14
                have you tried running the calls from management studio to see what the error is?  just saw your edit - good thing you found the issue
– Ruskin
                Jun 9, 2014 at 13:05
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, 'post','https://thecorrecturl:446/api/handelse/', '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
Hope this helps someone else
Regards
Caroline
Brian query works fine.
I you want to have API key add the following line
declare @apikey as nvarchar(32) = 'your api key';     
/*after content-type*/
exec sp_OAMethod @object, 'setRequestHeader', null, 'ApiKey', @apikey
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.