29th Sep 2020

What? Calling a POST API from SQL Server?

.NET

Written By, Uditsing Khati

Overview

Hello there. As a developer, in life, we need to do some challenging stuff to overcome the client’s expectations. We had some situations where we need to call some Post APIs from SQL Server. In this blog, we are going to discuss how we can call Post APIs from SQL Server. So, let’s start…

Introduction

Recently we came across a situation in our project where we needed to update user details when there is some data update in DB (One table is UserDetails and the second one is QueueLimitDetails).

The only solution for this that we could find, is firing API requests from the DB itself. We know this is a bit weird, but we gave it a try and it was a success.

To fire an API request, we head 2 possible ways.

  1. SQL agent
  2. Trigger

In our demo, we go through the above both possible ways.

sql-server

Our basic requirement is a Windows system with an MS SQL server. And, we have to use SQL Server Management Studio (SSMS) editor as an interface that is quite good as well as user-friendly.

Enable OLE Automation services

First, we need to enable OLE Automation services. If we run this SP without enabling OLE Automation services then we got the error.

Error:

exec-error-message

Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1 [Batch Start Line 2914] SQL Server blocked access to procedure ‘sys.sp_OADestroy’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

To resolve this error, we have to enable OLE Automation services. We need to execute the following query.

SQL Query

Here everyone is thinking about what OLE Automation services and why we need to enable this service?

Because by default this component is turned off as part of the security configuration for the server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, please check “Surface Area Configuration” in SQL Server Books.

Create a Stored Procedure for calling an API


You can find the Stored Procedure code here…

SP details

We need to check column ‘success’ value 1(true) or 0(false) from table QueueLimitDetails. If the column value is 0(False) then and only then we have to fire API requests. After successfully API we check API response status code. If the status code is 200 means our fired API has done their job successfully. So, we update the column ‘success’ to 1(true). By calling SP or API requests if we face/get any kind of errors then we have stored these error details into the table QueueLimitDetails_ErrorLog. So, we can see the error log and resolve it.

Create SQL agent for calling Stored Procedure

  1. Set Job Schedule
    new-job
  2. Give Job name.
    run-image
  3. Create steps to run Job Schedule
    New steps
  4. Select DB(Database) and write a command to execute SP (Store Procedure).
    Db command
  5. Set a schedule when calling a Stored Procedure.
    schedule-img

After set schedule press OK and complete your SQL agent setting. After that SP will get executed which will fire API requests every 1 minute!

Create a Trigger for calling Stored Procedure

After executing the above query your trigger will be created. This trigger will fire when your table data is updated or any new row added(new data entered). Trigger called SP and SP fired API requests.

Conclusion

You can use the SQL agent or trigger an API request. If any kind of error occurs, it will be stored in the error log table. We can use these ways to achieve call APIs from the SQL agent. Stay connected.

Written By,

.Net developer at Yudiz Solutions Pvt. Ltd