No coding issues here as such. I'm just pondering on best technique for retrieving external business data. The external data is accessible via REST API. Our SQL guy is suggesting that we call the Web API directly in SQL Server rather than using a VS Web API as an intermediary.
So option one, from SQL, retrieving external data (xp_cmdshell'ing a console app WebAPI) and then waiting on that to then populate the relevant tables would require a delay (WAITFOR) in the stored proc before proceeding on next steps in the stored proc (just so we know that the data has been returned (hopefully) in a timely manner). SQL guy is opposed to this but I though this was the best technique.
Option two, as I say, would be calling the external API from the stored proc itself (sp_OACreate). He's suggesting that this would be better
So I'm just wondering on best approach to this and would appreciate some feedback and why you have chose one or two, or maybe something completely different?
If option one is the most favourable, is xp_cmdshell'ing a console app the best way to do this or are there other ways?
