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

Views: 2.439 views

In this article

Reading Time: 5 minutes

Hello everybody!
Alright?

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: //websro.correios.com.br…, 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 http://www.linkcorreios.com.br/, 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

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]
    @Ds_Rastreamento [varchar](13)
AS
BEGIN


    SET NOCOUNT ON

        
    ------------------------------------------------------------------------
    -- RECUPERAÇÃO DAS INFORMAÇÕES
    ------------------------------------------------------------------------

    DECLARE 
        @obj INT,
        @Url VARCHAR(8000),
        @xml VARCHAR(MAX),
        @resposta VARCHAR(MAX)
        

    SET @Url = 'http://www.linkcorreios.com.br/?id=' + @Ds_Rastreamento


    EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1
    EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false
    EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC sys.sp_OAMethod @obj, 'send'
 
 
    IF (OBJECT_ID('tempdb..#xml') IS NOT NULL) DROP TABLE #xml
    CREATE TABLE #xml ( 
        Ds_Dados VARCHAR(MAX)
    )

    INSERT INTO #xml(Ds_Dados)
    EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT
    
    
    EXEC sys.sp_OADestroy @obj
 
 
    SELECT TOP 1 @resposta = Ds_Dados FROM #xml

    
    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    
    ------------------------------------------------------------------------
    -- TRATAMENTO DOS DADOS
    ------------------------------------------------------------------------

    SET @xml = REPLACE(@xml, '</td><td>', ' | ')
    
    IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id) AS Linha,
        LTRIM(RTRIM(REPLACE(REPLACE(Palavra, CHAR(13), ''), CHAR(9), ''))) AS Palavra
    INTO
        #Dados
    FROM
        dbo.fncSplitTexto(dbo.fncRemove_HTML(@xml), CHAR(10))
    WHERE
        NULLIF(LTRIM(RTRIM(REPLACE(REPLACE(Palavra, CHAR(13), ''), CHAR(9), ''))), '') IS NOT NULL
        
    
    ------------------------------------------------------------------------
    -- RESULTADO FINAL
    ------------------------------------------------------------------------
    
    DECLARE 
        @Linha_Inicio INT = (SELECT Linha FROM #Dados WHERE Palavra = 'Data / Hora') + 2,
        @Linha_Fim INT = (SELECT Linha FROM #Dados WHERE Palavra LIKE '%Objeto atrasado? Problema na entrega?%')

    IF (OBJECT_ID('tempdb..#Dados_Final') IS NOT NULL) DROP TABLE #Dados_Final
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Linha) AS Linha,
        Palavra AS Rastreamento,
        (Linha % 3) AS Grupo
    INTO 
        #Dados_Final
    FROM 
        #Dados
    WHERE 
        Linha >= @Linha_Inicio
        AND Linha < @Linha_Fim


    DECLARE @Tabela_Final TABLE (
        Dt_Rastreamento DATETIME,
        Ds_Rastreamento VARCHAR(255),
        Ds_Local VARCHAR(255)
    )

    DECLARE
        @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Dados_Final) / 3

    WHILE (@Contador < @Total)
    BEGIN
            
        INSERT INTO @Tabela_Final
        SELECT
            CONVERT(DATETIME, (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 1), 103) AS Dt_Rastreamento,
            (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 2) AS Ds_Rastreamento,
            REPLACE((SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 3), 'Local: ', '') AS Ds_Local

        SET @Contador += 1

    END


    SELECT * FROM @Tabela_Final

    
END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]

@Ds_Rastreamento [varchar](13)

BEGIN

SET NOCOUNT ON

------------------------------------------------------------------------

-- RECUPERAÇÃO DAS INFORMAÇÕES

------------------------------------------------------------------------

DECLARE

@obj INT,

@Url VARCHAR(8000),

@xml VARCHAR(MAX),

@resposta VARCHAR(MAX)

SET @Url = 'http://www.linkcorreios.com.br/?id=' + @Ds_Rastreamento

EXEC sys.sp_OACreate @progid = 'MSXML2.ServerXMLHTTP', @objecttoken = @obj OUT, @context = 1

EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false

EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'

EXEC sys.sp_OAMethod @obj, 'send'

IF (OBJECT_ID('tempdb..#xml') IS NOT NULL) DROP TABLE #xml

CREATE TABLE #xml (

Ds_Dados VARCHAR(MAX)

)

INSERT INTO #xml(Ds_Dados)

EXEC sys.sp_OAGetProperty @obj, 'responseText' --, @resposta OUT

EXEC sys.sp_OADestroy @obj

SELECT TOP 1 @resposta = Ds_Dados FROM #xml

SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS

------------------------------------------------------------------------

-- TRATAMENTO DOS DADOS

------------------------------------------------------------------------

SET @xml = REPLACE(@xml, '</td><td>', ' | ')

IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados

SELECT

ROW_NUMBER() OVER(ORDER BY Id) AS Linha,

LTRIM(RTRIM(REPLACE(REPLACE(Palavra, CHAR(13), ''), CHAR(9), ''))) AS Palavra

INTO

#Dados

FROM

dbo.fncSplitTexto(dbo.fncRemove_HTML(@xml), CHAR(10))

WHERE

NULLIF(LTRIM(RTRIM(REPLACE(REPLACE(Palavra, CHAR(13), ''), CHAR(9), ''))), '') IS NOT NULL

------------------------------------------------------------------------

-- RESULTADO FINAL

------------------------------------------------------------------------

DECLARE

@Linha_Inicio INT = (SELECT Linha FROM #Dados WHERE Palavra = 'Data / Hora') + 2,

@Linha_Fim INT = (SELECT Linha FROM #Dados WHERE Palavra LIKE '%Objeto atrasado? Problema na entrega?%')

IF (OBJECT_ID('tempdb..#Dados_Final') IS NOT NULL) DROP TABLE #Dados_Final

SELECT

ROW_NUMBER() OVER(ORDER BY Linha) AS Linha,

Palavra AS Rastreamento,

(Linha % 3) AS Grupo

INTO

#Dados_Final

FROM

#Dados

WHERE

Linha >= @Linha_Inicio

AND Linha < @Linha_Fim

DECLARE @Tabela_Final TABLE (

Dt_Rastreamento DATETIME,

Ds_Rastreamento VARCHAR(255),

Ds_Local VARCHAR(255)

)

DECLARE

@Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Dados_Final) / 3

WHILE (@Contador < @Total)

BEGIN

INSERT INTO @Tabela_Final

SELECT

CONVERT(DATETIME, (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 1), 103) AS Dt_Rastreamento,

(SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 2) AS Ds_Rastreamento,

REPLACE((SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 3), 'Local: ', '') AS Ds_Local

SET @Contador += 1

END

SELECT * FROM @Tabela_Final

END

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

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]
    @Ds_Rastreamento [varchar](13)
AS
BEGIN
 
    ------------------------------------------------------------------------
    -- RECUPERAÇÃO DAS INFORMAÇÕES
    ------------------------------------------------------------------------

    DECLARE 
        @obj INT,
        @Url VARCHAR(8000),
        @xml VARCHAR(MAX),
        @resposta VARCHAR(MAX)
        

    SET @Url = 'http://www.linkcorreios.com.br/?id=' + @Ds_Rastreamento


    EXEC CLR.dbo.stpWs_Requisicao
        @Ds_Url = @Url, -- nvarchar(max)
        @Ds_Metodo = N'GET', -- nvarchar(max)
        @Ds_Parametros = N'', -- nvarchar(max)
        @Ds_Codificacao = N'UTF-8', -- nvarchar(max)
        @Ds_Retorno_OUTPUT = @resposta OUTPUT -- nvarchar(max)
    

    SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
    

    ------------------------------------------------------------------------
    -- TRATAMENTO DOS DADOS
    ------------------------------------------------------------------------

    SET @xml = REPLACE(@xml, '</td><td>', ' | ')

    
    IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id) AS Linha,
        LTRIM(RTRIM(REPLACE(Ds_Palavra, CHAR(13), ''))) AS Palavra
    INTO
        #Dados
    FROM
        CLR.dbo.fncSplit_Texto(CLR.dbo.fncRemove_HTML(@xml), CHAR(10), 1)
    WHERE
        NULLIF(REPLACE(Ds_Palavra, CHAR(13), ''), '') IS NOT NULL
    
    ------------------------------------------------------------------------
    -- RESULTADO FINAL
    ------------------------------------------------------------------------
    
    DECLARE 
        @Linha_Inicio INT = (SELECT Linha FROM #Dados WHERE Palavra = 'Data / Hora') + 2,
        @Linha_Fim INT = (SELECT Linha FROM #Dados WHERE Palavra LIKE '%Objeto atrasado? Problema na entrega?%')

    IF (OBJECT_ID('tempdb..#Dados_Final') IS NOT NULL) DROP TABLE #Dados_Final
    SELECT 
        ROW_NUMBER() OVER(ORDER BY Linha) AS Linha,
        Palavra AS Rastreamento,
        (Linha % 3) AS Grupo
    INTO 
        #Dados_Final
    FROM 
        #Dados
    WHERE 
        Linha >= @Linha_Inicio
        AND Linha < @Linha_Fim


    DECLARE @Tabela_Final TABLE (
        Dt_Rastreamento DATETIME,
        Ds_Rastreamento VARCHAR(255),
        Ds_Local VARCHAR(255)
    )

    DECLARE
        @Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Dados_Final) / 3

    WHILE (@Contador < @Total)
    BEGIN
            
        INSERT INTO @Tabela_Final
        SELECT
            CONVERT(DATETIME, (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 1), 103) AS Dt_Rastreamento,
            (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 2) AS Ds_Rastreamento,
            REPLACE((SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 3), 'Local: ', '') AS Ds_Local

        SET @Contador += 1

    END


    SELECT * FROM @Tabela_Final
	

END

CREATE PROCEDURE [dbo].[stpBusca_Rastreamento_Correios]

@Ds_Rastreamento [varchar](13)

BEGIN

------------------------------------------------------------------------

-- RECUPERAÇÃO DAS INFORMAÇÕES

------------------------------------------------------------------------

DECLARE

@obj INT,

@Url VARCHAR(8000),

@xml VARCHAR(MAX),

@resposta VARCHAR(MAX)

SET @Url = 'http://www.linkcorreios.com.br/?id=' + @Ds_Rastreamento

EXEC CLR.dbo.stpWs_Requisicao

@Ds_Url = @Url, -- nvarchar(max)

@Ds_Metodo = N'GET', -- nvarchar(max)

@Ds_Parametros = N'', -- nvarchar(max)

@Ds_Codificacao = N'UTF-8', -- nvarchar(max)

@Ds_Retorno_OUTPUT = @resposta OUTPUT -- nvarchar(max)

SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS

------------------------------------------------------------------------

-- TRATAMENTO DOS DADOS

------------------------------------------------------------------------

SET @xml = REPLACE(@xml, '</td><td>', ' | ')

IF (OBJECT_ID('tempdb..#Dados') IS NOT NULL) DROP TABLE #Dados

SELECT

ROW_NUMBER() OVER(ORDER BY Id) AS Linha,

LTRIM(RTRIM(REPLACE(Ds_Palavra, CHAR(13), ''))) AS Palavra

INTO

#Dados

FROM

CLR.dbo.fncSplit_Texto(CLR.dbo.fncRemove_HTML(@xml), CHAR(10), 1)

WHERE

NULLIF(REPLACE(Ds_Palavra, CHAR(13), ''), '') IS NOT NULL

------------------------------------------------------------------------

-- RESULTADO FINAL

------------------------------------------------------------------------

DECLARE

@Linha_Inicio INT = (SELECT Linha FROM #Dados WHERE Palavra = 'Data / Hora') + 2,

@Linha_Fim INT = (SELECT Linha FROM #Dados WHERE Palavra LIKE '%Objeto atrasado? Problema na entrega?%')

IF (OBJECT_ID('tempdb..#Dados_Final') IS NOT NULL) DROP TABLE #Dados_Final

SELECT

ROW_NUMBER() OVER(ORDER BY Linha) AS Linha,

Palavra AS Rastreamento,

(Linha % 3) AS Grupo

INTO

#Dados_Final

FROM

#Dados

WHERE

Linha >= @Linha_Inicio

AND Linha < @Linha_Fim

DECLARE @Tabela_Final TABLE (

Dt_Rastreamento DATETIME,

Ds_Rastreamento VARCHAR(255),

Ds_Local VARCHAR(255)

)

DECLARE

@Contador INT = 0, @Total INT = (SELECT COUNT(*) FROM #Dados_Final) / 3

WHILE (@Contador < @Total)

BEGIN

INSERT INTO @Tabela_Final

SELECT

CONVERT(DATETIME, (SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 1), 103) AS Dt_Rastreamento,

(SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 2) AS Ds_Rastreamento,

REPLACE((SELECT Rastreamento FROM #Dados_Final WHERE Linha = (@Contador * 3) + 3), 'Local: ', '') AS Ds_Local

SET @Contador += 1

END

SELECT * FROM @Tabela_Final

END

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

Francisco said:

December 6 from 2020 to 18: 03

Good night Dirceu. Great job, I ran it on the server but it's returning zero. Would you know to give me a hint if I'm doing something wrong in SQL server

Marcos F. Rodrigues said:

29 from 2019 to 12 at 31: XNUMX

Excellent, I didn't know anything about SQL Server. very good. I already use Link Post a few years to send to customers of my wife's store, they also let put logo of the store for those who have e-commerce. They are really very good. These other sites are slow and fall down. LinkCorreios.com.br is amazing because everyone falls and they don't. Could you teach something similar using the http://www.supertrack.com.br to track Fedex and JadLog?

- Dirceu Resende said:
  
  18 from 2019 from September to 23: 00
  
  I am adding to my list of posts. Thanks for the tip 🙂
  
Paulo Chaves said:

December 6 from 2018 to 18: 25

Hi Dirceu,
I really liked your article, I had already done something similar using the api of the website Link & Track (www.linketrack.com), they return the answer in JSON, CSV OR XML which makes it easier to manipulate the answer, instead scrap HTML.

- Dirceu Resende said:
  
  December 7 from 2018 to 07: 55
  
  Good tip, huh Paulo! I'll take a look and make article soon about it 😉
  Thanks!

Websro Post Office | www.websro.com.br - Login

December 3, 2021

[…] SQL Server – How to track orders and objects from … […]

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

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

How to track objects using OLE Automation

View SP source code

How to track objects using CLR

View SP source code

Result of SP's

sql server tsql transact sql query tracking parcel post objects

You may also like ...

Comments

Leave a Comment Cancel reply

How to track objects using OLE Automation

View SP source code

How to track objects using CLR

View SP source code

Result of SP's

sql server tsql transact sql query tracking parcel post objects

You may also like ...

Enabling and using dedicated remote administrator connection (DAC) in SQL Server

SQL Server - How to view the entire return message from Job execution (even when it exceeds 4000 characters)

SQL Server - How to Avoid Brute Force Attacks on Your Database

Comments

Leave a Comment Cancel reply