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.
- SQL agent
- Trigger
In our demo, we go through the above both possible ways.
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:
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
sp_configure 'show advanced options', 1; GO RECONFIGURE sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
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
USE [DBName] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Author :Uditsing Khati Create date :--/--/2020 Description :Update Details Organization:Yudiz Solutions Pvt. Ltd. Modified : Test : [dbo].[QueueLimitDetails_Update] */
…
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
- Set Job Schedule
- Give Job name.
- Create steps to run Job Schedule
- Select DB(Database) and write a command to execute SP (Store Procedure).
- Set a schedule when calling a Stored Procedure.
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
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[QueueLimitDetails_Insert_Update] ON [dbo].[QueueLimitDetails] AFTER INSERT, UPDATE AS BEGIN SET NOCOUNT ON; BEGIN -- Call SP here EXEC [dbo].[QueueLimitDetails_Update] END -- Call SP here END
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.
You may also like
Unlock .NET Core Development with Cutting-Edge Strategies
As the software development industry adapts, Core has become a dominant framework that allows progra...
How can you use ML Sentiment Analysis in ASP.NET Core?
We are going to talk about ML Sentiment Analysis which is able to conveniently explore the aspects o...
How to work Loop Execution with Benchmark using .NET
What is Benchmarking? It is used for measuring timing complexity for various algorithms and part of ...
