Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.456 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 8 de fevereiro de 2015
  • 0
  • Banco de Dados Desenvolvimento de Query Erros SQL Server

Arithmetic Overflow na view de catálogo sys.syscolumns do SQL Server 2008

Visualizações: 189
Tempo de Leitura: 3 minutos

Olá, Pessoal!
Tudo bem ?

Hoje vou falar sobre um problema que encontrei recentemente, onde uma rotina utilizava a view de catálogo sys.syscolumns para obter informações das colunas de uma tabela Fato (BI) e a partir de um determinado momento, ao tentar executar a query SELECT * FROM sys.syscolumns, o SQL Server retornava a seguinte mensagem:

Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 40003.

Essa era a primeira vez que eu via uma simples consulta numa view do catálogo do banco retornar um erro.. Então vamos analisar o nosso cenário:

      1) A mensagem de erro nos diz que houve um estouro num campo smallint, cujo valor seria 40003 (limite do smallint: 32767)
      2) Como é uma view do catálogo, não existe opção de alterá-la para aumentar o tamanho da coluna
      3) As colunas da view sys.syscolumns que possuem o tipo smallint são: xusertype, length, colid, xoffset, colstat, number, colorder, offset, usertype, prec
      4) Analisando as colunas smallint, podemos observar que as únicas colunas onde podemos influenciar nos valores, são as colunas colid e colorder, que são identificadores da coluna dentro da tabela (objeto pai) e identificador da ordem da coluna (geralmente as duas colunas tem o mesmo valor), que podem variar de acordo com a quantidade de colunas da nossa tabela

Suspeitando dessas colunas e tendo em mente que elas são preenchidas com auto-incremento, imaginei que por algum motivo, essa rotina de BI poderia estar apagando e recriando colunas na tabela Fato diariamente, fazendo com que esse número só aumentasse e em um determinado momento, atingisse o limite do tipo de dado smallint. Verifiquei com o analista que era responsável pela rotina que apresentava essa falha e ele me confirmou a suspeita. Realmente a rotina apaga e criava colunas diariamente.

Após confirmar o problema de origem, partimos para a solução por ora, que foi criar uma outra tabela com a mesma estrutura da tabela original, copiar os dados para essa outra tabela, apagar a tabela original e renomear a nova tabela para o nome original. Desta forma, as colunas colid e colorder desta tabela seria “resetados” para 1, 2, 3… A solução final deve ser feita na rotina, mas pelo menos, conseguimos colocar a rotina para funcionar e não deixamos que outras rotinas/usuários fiquem impossibilitados de utilizar essa view de catálogo do banco por causa de uma tabela.

Uma solução rápida e que também poderia resolver o problema por ora, seria alterar a rotina de ETL para utilizar uma das opções abaixo, que possuem a coluna de definição da ordem do tipo int, cujo limite é 2.147.483.647:

  • sys.all_columns (column_id)
  • sys.columns (column_id)
  • INFORMATION_SCHEMA.COLUMNS (ORDINAL_POSITION)

Caso você queira simular esse caso, segue script abaixo:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- Consultando a view
SELECT * FROM sys.syscolumns
 
 
-- Criando a estrutura da tabela
IF (OBJECT_ID('Testes.dbo.Estouro_Sys_Columns') IS NOT NULL) DROP TABLE Testes.dbo.Estouro_Sys_Columns
CREATE TABLE Testes.dbo.Estouro_Sys_Columns (
    Id INT IDENTITY(1,1),
    Nome VARCHAR(50),
    Tmp_Coluna1 VARCHAR(20),
    Tmp_Coluna2 VARCHAR(20),
    Tmp_Coluna3 VARCHAR(20),
    Tmp_Coluna4 VARCHAR(20)
)
 
CREATE CLUSTERED INDEX SK01_Id ON dbo.Estouro_Sys_Columns(Id)
 
 
 
-- Simulando a rotina sendo executada várias vezes
 
DECLARE @Contador INT = 1, @Total INT = 10000
 
WHILE(@Contador <= @Total)
BEGIN
    
    ALTER TABLE Testes.dbo.Estouro_Sys_Columns DROP COLUMN
        Tmp_Coluna1,
        Tmp_Coluna2,
        Tmp_Coluna3,
        Tmp_Coluna4
        
        
    ALTER TABLE Testes.dbo.Estouro_Sys_Columns ADD
        Tmp_Coluna1 VARCHAR(20),
        Tmp_Coluna2 VARCHAR(20),
        Tmp_Coluna3 VARCHAR(20),
        Tmp_Coluna4 VARCHAR(20)
        
    
    SET @Contador = @Contador + 1
    
END
 
 
-- Tentando consultar a view novamente
SELECT * FROM sys.syscolumns -- ERRO! Arithmetic overflow error for data type smallint, value = 40003.
 
 
-- CORRIGINDO O PROBLEMA - Criação da estrutura igual à tabela original
IF (OBJECT_ID('Testes.dbo.Estouro_Sys_Columns2') IS NOT NULL) DROP TABLE Testes.dbo.Estouro_Sys_Columns2
CREATE TABLE Testes.dbo.Estouro_Sys_Columns2 (
    Id INT IDENTITY(1,1),
    Nome VARCHAR(50),
    Tmp_Coluna1 VARCHAR(20),
    Tmp_Coluna2 VARCHAR(20),
    Tmp_Coluna3 VARCHAR(20),
    Tmp_Coluna4 VARCHAR(20)
)
 
CREATE CLUSTERED INDEX SK01_Id ON dbo.Estouro_Sys_Columns2(Id)
 
 
-- CORRIGINDO O PROBLEMA - Inserindo os dados da tabela (caso haja)
SET IDENTITY_INSERT dbo.Estouro_Sys_Columns2 ON
 
INSERT INTO dbo.Estouro_Sys_Columns2 (
    Id,
    Nome,
    Tmp_Coluna1,
    Tmp_Coluna2,
    Tmp_Coluna3,
    Tmp_Coluna4
)
SELECT * FROM dbo.Estouro_Sys_Columns
 
 
-- CORRIGINDO O PROBLEMA - Apagando a tabela antiga e renomeando a nova
DROP TABLE dbo.Estouro_Sys_Columns
EXEC sp_rename 'dbo.Estouro_Sys_Columns2', 'Estouro_Sys_Columns'
 
 
-- Consultando a view
SELECT * FROM sys.syscolumns -- WHERE id = OBJECT_ID('dbo.Estouro_Sys_Columns')

Tags: casoscatalogcatálogooverflowsmallintsqlsql serversyssyscolumnsview

You may also like...

  • SQL Server – Como ocultar os databases para usuários não autorizados

  • SQL Server – String or binary data would be truncated: O que é, como identificar a causa raiz e como corrigir

  • Certificações Microsoft – Dicas, links e materiais de estudo para as provas de MCSA e MCSE do SQL Server 2016

  • Next Identificando e resolvendo problemas de usuários órfãos no SQL Server com a sp_change_users_login
  • Previous Protheus – Nomes e Descrições das tabelas do ERP da TOTVS

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.