While I understand that using SQL to make REST calls to a web service might not be advisable, I have a system where it’s a good functional fit.
declare @object as int, @url varchar(5000)--, @response varchar(8000)
declare @responsetext as varchar(1000);
select @url = '[WEBSERVICE]'
exec sp_OACreate 'MSXML2.XMLHTTP', @object out;
exec sp_oamethod @object, 'open', null, 'get', @url
exec sp_oamethod @object, 'send'
exec sp_OADestroy @Object
I have found that after some time, the REST calls stop working. I did some experimenting and determined that it stops after exactly 8192 calls. When it stops, if you call that web service from a browser, it works fine. The only way I’ve found to get it working again is to restart SQL services. Does anyone know of a way to flush, clear, or do something proactively to prevent it from stopping after the 8192 calls?