Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to Track Orders and Mail Objects (After WEBSRO Deactivated)

Views: 2.439 views
Reading Time: 5 minutes

Hello everybody!

In this post today I will bring something very cool, which is a Transact-SQL stored procedure where you can query and track order information and objects using data from the Post Office site in SQL Server. For this, I will use the MSXML2.ServerXMLHTTP object and OLE Automation procedures for one solution and the CLR for another alternative.

This is very useful for e-commerce companies that can use this SP to integrate Post Office tracking with their systems and display on-screen delivery of their products.

I had already shown a very similar solution to this in the article Querying SQL Server object tracking, but this solution stopped working on June 10, when Correios decided to disable the address that was executed on the link http: //…, used by several e-Commerce systems to do the tracking of objects and in my previous post.

With that, I had to think of another solution for tracking objects. Through some internet searches, I found the service, which allows you to track very accurately. Let's see how it will allow us to track objects.

How to track objects using OLE Automation

A very simple and quick way to make HTTP Request requests by SQL Server is using the OLE Automation feature, which invokes external libraries and DLL's to perform various tasks through the database. Look on this link some other posts about OLE Automation.

Before we get started, let's look at some prerequisites for using this procedure.:
- Enable the OLE Automation feature on your SQL Server (see how this post)
- fncRemove_HTML function to remove HTML tags from a varchar string (see more this post)
- fncSplitTexto function to break a string into substrings using a delimiter (see more this post)

An important detail I noticed in the development of this SP is that if the amount of XML characters returned by the sp_OAGetProperty procedure is greater than 4000 characters, the SP return will be NULL, since the sp_OAGetProperty procedure has this limitation of 4000 characters. To work around this problem, instead of using an OUTPUT variable to capture the data, I insert it into a temporary table and then work with the data. 🙂

View SP source code

Click here to expand

How to track objects using CLR

Another way to be able to perform HTTP requests from SQL Server is by using CLR, a powerful tool available from Microsoft that lets you create programs written in C # or VB, using all the features of the Microsoft .NET Framework, and run them from the database. of data. If you do not already know the CLR, take a read this link here and follow all my articles using CLR on this link.

Prerequisites for using this procedure:
- fncRemove_HTML function to remove HTML tags from a varchar string (see more this post)
- fncSplit_Texto function to wrap HTML in lines (see more this post)
- Stored Procedure stpWs_Requisicao to perform HTTP requests (see more this post)

View SP source code

Click here to expand

Result of SP's

Both Stored Procedures have exactly the same return. Only the coding of the two differs. Once you choose which of the two approaches you want to follow, the result of SP will look like this:

That's it folks!
I hope you enjoyed this post and leave your comment.

sql server tsql transact sql query tracking parcel post objects

sql server tsql transact sql query tracking parcel post objects