SQL Server - How to query the national, state, municipal, and optional holidays of an API using OLE Automation and CLR (C #)

Views: 2.938
Reading Time: 3 minutes

Hello people,
Good afternoon!

In this post, I will follow a reader's tip and use the great free API of the site Calendar.com to return to the list of national, state, municipal and facultative holidays of Brazil, allowing to filter by year, state and / or city. I had already commented on this subject in the post How to create a table of holidays (national, state, and mobile) in SQL Server, but I think this post will bring one more way to accomplish this task.

I believe the solution of this post is more complete than my previous post of holidays, because it has even the municipal and optional holidays. However, each query generates an HTTP request, which applied to a large list of cities / years could take several seconds, while the solution of the other post already calculates and stores all data in a table, which does not prevent this from being done. also made with the solution via API.

To learn more about the OLE Automation feature, click this link.

Using OLE Automation

The fastest way to implement this is by using the OLE Automation MSXML2.ServerXMLHTTP object, which allows you to perform XML requests through the HTTP protocol.

As I have commented in other posts, for reasons of security and stability of the environment, I do NOT recommend activating and using the OLE Automation feature, but I wanted to demonstrate here a solution on how to use this powerful but dangerous database feature.

View source

Example of use:
sql-server-how-to-generate-a-state-municipal-holidays-table-with-api-ole-automation

Using SQL CLR (C #)

Already well-known here from blog posts, CLR allows you to use .NET Framework C # or VB.NET code within the SQL Server database, thereby gaining new features and extending the capacity of the database.

If you don't know CLR, or don't know how it works and what it is, visit this link.

View source

sql-server-how-to-list-state-municipal-holidays-on-clr

That's it folks!
Thanks for visiting, I hope you enjoyed and see you in the next post.