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

Querying SQL Server object tracking

Views: 2.307 views
Reading Time: 3 minutes

Hello people,
Good afternoon!

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.

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.

UPDATE: From day 10 / 06 / 2017 this feature stopped working, since Correios decided to disable the WEBSRO service, which everyone used to quickly consult the tracking of objects. However, I made the post SQL Server - How to Track Orders and Mail Objects (After WEBSRO Deactivated) that circumvents this problem. 🙂

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)
- fncSplit_Texto 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. 🙂

SP usage examples

SQL Server - Object Tracking Mail Order

Implementing the Stored Procedure in Your Database

View source

That's it!
Did you like this procedure? Now you can create tracking for your orders .. kkkk

sql server tsql transact sql query tracking parcel post objects

sql server tsql transact sql query tracking parcel post objects