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:
| 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') |
