t-sql to connect to an api like adp api with oauth rest and insert the response in a table

Question

Is it possible to use t-sql to connect to an api like adp api with oauth rest and insert the response in a table? And how do you do that?

Answer 1

You would that with a CLR procedure, that is a procedure written in C#. You can also use COM through sp_OAcreate & co, but that's horrible. CLR is the best option.

Answer 2

Hi @TomHines-8657,

As agreed with Erland, you could try with CLR Procedure.

By using CLR, you could run and manage the code inside the SQL Server. Code that runs within CLR is referred to as a managed code.

You could create the stored procedures, triggers, user defined types and user-defined aggregates in the managed code. You could achieve significant performance increases because the managed code compiles to the native code prior to the execution.

You could refer Calling Web Service From SQL Server Using SQL CLR for more details about the steps one by one.

In addition, if SSIS is permitted, you could also refer below links:
HTTP POST in SSIS – Send data to Web API url
SSIS post data to web service API

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table