Hello people!
Alright?
Today I am going to talk about a problem I recently encountered, where a routine used the sys.syscolumns catalog view to get column information from a Fact (BI) table and at some point when trying to execute the SELECT * FROM sys query .syscolumns, SQL Server returned the following message:
Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 40003.
This was the first time I'd seen a simple query in a bank catalog view return an error. So let's look at our scenario:
- 1) The error message tells us that there was an overflow in a smallint field whose value would be 40003 (smallint limit: 32767)
- 2) Since it is a catalog view, there is no option to change it to increase column size.
- 3) The sys.syscolumns view columns that have type smallint are: xusertype, length, colid, xoffset, colstat, number, colorder, offset, usertype, prec
- 4) Looking at smallint columns, we can see that the only columns where we can influence values are the colid and colorder columns, which are column identifiers within the table (parent object) and column order identifier (usually both columns have same value), which may vary depending on the number of columns in our table
Suspecting these columns and keeping in mind that they are filled with auto-increment, I figured for some reason this BI routine could be erasing and recreating columns in the Fact table daily, causing that number to only increase and at some point, reached the limit of the smallint data type. I checked with the analyst who was responsible for the routine that presented this flaw and he confirmed the suspicion. Really the routine erases and creates columns daily.
After confirming the source problem, we started the solution for now, which was to create another table with the same structure as the original table, copy the data to that other table, delete the original table and rename the new table to its original name. In this way, the columns colid and colorder of this table would be "reset" to 1, 2, 3 ... The final solution must be made in the routine, but at least, we managed to put the routine to work and we do not let other routines / users become impossible to use this bank catalog view because of a table.
A quick solution that could also solve the problem for now would be to change the ETL routine to use one of the options below, which have the int order column, whose limit is 2.147.483.647:
- sys.all_columns (column_id)
- sys.columns (column_id)
- INFORMATION_SCHEMA.COLUMNS (ORDINAL_POSITION)
If you want to simulate this case, follow script below:
|
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') |
