Problema de tamanho de string no retorno de um JSON

Question

Criei uma procedure que consome uma API get e me retorna uma lista de clientes. Esse retorno estou gravando um uma tabela.

Quando eu executo a procedure pela Query do SQL, ela funciona direitinho, me traz o resultado JSON completo, mas quando configuro essa procedure para executar em uma JOB, o retorno do JSON quebra.

Exemplo, quando rodo pela Query o JSON me retorna 5141 caracteres, mas quando rodo essa mesma procedure via JOB, só retorna 512 caracteres.

Que problema deve ser isso? A JOB limita uma quantidade de retorno na string?

Answer 1

Cesar,

Qual é a versão do SQL Server que você esta utilizando? Para trabalhar com JSON o suporte para este formato de dado esta disponível a partir da versão 2016!!! Você esta utilizando a função JSON_Value?

Você esta tentando manipular este dado no formato Varchar(MAX)? 

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 2

Junior,

A versão do SQL é a 2016. 

Não estou utilizando a função JSON_Value, estou tentando manipular usando o varchar(max).

Vou postar aqui uma parte do código.

declare

       @authHeader VARCHAR(8000),

       @contentType VARCHAR(8000),

       @ret INT,

       @token INT,

       @url VARCHAR(8000)

declare @Tab_Response table (Response varchar(max));

SET @authHeader = 'minha chave';

SET @contentType = 'application/json';

SET @url = 'https://minha url;

--Select @IDMapa, @Endereco, @url

-- Open the connection.

EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;

IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.

EXEC @ret = sp_OAMethod @token, 'Open', null, 'GET', @Url, 'false'

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;

--EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'dh_init', '10/03/2020';

--Chamada para método SEND

EXEC sp_OAMethod @token, 'send', null

IF OBJECT_ID(N'tempdb..[#TempResult]') IS NOT NULL

BEGIN

Drop table [#TempResult]

END

CREATE TABLE #TempResult(Response varchar(max))

      

INSERT into #TempResult (Response)

EXEC sp_OAMethod @token, 'responseText'

--Quando rodo via query, o response vem completo, quando rodo via JOB ele quebra

select Response from #TempResult

declare

       @apiJSON varchar(max),

       @IsValid bit,

       @DataItem varchar(max)

SET @apiJSON = (select Response from #TempResult)

--Checar se é um JSON válido

IF (ISJSON(@apiJSON) = 1)

BEGIN

    

       SET @IsValid = (SELECT [value] FROM OPENJSON(@apiJSON) WHERE [key] = 'IsValid')

       IF (@IsValid = 1)

       BEGIN

             SET @DataItem = (SELECT [value] FROM OPENJSON (@apiJSON)  WHERE [key] = 'DataItem')

            

             TRUNCATE TABLE TBIMP_Visitantes

            

             INSERT INTO TBIMP_Visitantes (cd_visitante, nm_visitante, email)

                                                 SELECT cd_visitante, nm_visitante, email FROM OPENJSON (@DataItem)

             WITH

             (

                    cd_visitante varchar(200) '$.cd_visitante',

                    nm_visitante varchar(200) '$.nm_visitante',

                    email varchar(200) '$.email'

                   

             )

       END

   

END

Answer 3

Cesar,

Aparentemente não vejo um problema no seu código, pois como você destacou o mesmo esta funcionando corretamente quando é processado diretamente no Management Studio.

Tenho uma ressalva que seria interessante analisar, em algumas partes do código você esta declarando variáveis com o tipo de dados Varchar(Max), mas faz uso da função OpenJSON() em outros trechos, por padrão esta função trabalhar melhor com o tipo de dados NVarchar(Max) para garantir justamente o retorno confiável e legível de qualquer formato ou valor, talvez, repito talvez, seja interessante fazer esta mudança no seu código fonte para garantir o retorno integral dos dados contidos no JSON.

Independente disso, gostaria de dar uma sugestão, transforme esta código em uma Stored Procedure e ao invês de passar todo código diretamente no Job, declare somente o comando que executa a Stored Procedure no Step do seu Job.

Por gentileza, posteriormente nos traga um feedback desta sugestão.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 4

Boa noite Junior,

Eu já estava usando Stored Procedure “spu_PesquisarVisitantes”. Fazendo exatamente como você sugeriu, executando a Stored Procedure no Step do Job.

Troquei no código as variáveis varchar para nvarchar(max), mesmo assim o problema continua.

Criei uma tabela no banco sem ser temporária para ver o que está vindo do Response.

insert into Teste (Response)

EXEC sp_OAMethod @token, 'responseText'

Logo nesse trecho o response vem quebrado, nem chego a manipular com a função OpenJSON() .

Executando a procedure diretamente no Management Studio esse response vem com 5141 caracteres, mas executando a mesma procedure no Step do Job vem somente 512 caracteres.

Aparentemente o Job está limitando o resultado do get da API. É bem estranho, não estou encontrando uma lógica nisso.

Answer 5

Cesar,

Seria possível trocar o tipo de dados Int utilizado para a variável @Token para NVarchar()?

Caso não sejá possível, pensei em você gravar o conteúdo de saída em um arquivo texto. O que acha?

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]