This example is very similar to the API that I am calling.
The @ResponseText is VARCHAR(8000) but when it gets populated by 'responseText' with either sp_OAMethod or sp_OAGetProperty, it only fills up to about 900 characters.
When I call the same API through C#, I get the full ResponseText which is about 3000 characters.
Originally, I thought @ResponseText VARCHAR(8000) might be the problem. But the more I think about it, it might be sp_OA methods since it is calling in chunks? Has anyone experience this?
Declare @Object as Int; Declare @ResponseText as Varchar(8000); Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; Exec sp_OAMethod @Object, 'open', NULL, 'get','http://server/ws/service1.asmx/Test', 'false'; Exec sp_OAMethod @Object, 'send'; Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT; EXEC sp_OAGetErrorInfo @Object Select @ResponseText Resultado; Exec sp_OADestroy @Object;
I got the code above from here: https://stackoverflow.com/questions/14259630/how-to-call-webservice-from-tsql-sql-server-2000