Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • CONSUTING
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.868 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft products (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • Consultancy (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 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') November 5th, 2020
  • February 8rd, 2015
  • 0
  • Database Query Development Errors SQL Server

Arithmetic Overflow in SQL Server 2008 sys.syscolumns Catalog View

Views: 202
Reading Time: 3 minutes

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:

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: casescatalogCatalogueOverflowsmallintsqlsql serversyssyscolumnsview

You may also like ...

  • SQL Server - The danger of using JOIN between columns of different data types

  • SQL Server 2012 - How to create pagination of data in the results of a query with OFFSET and FETCH

  • SQL Server - How to improve the output formatting of queries combined with SET STATISTICS IO and TIME

  • Next Identifying and solving orphaned user problems in SQL Server with sp_change_users_login
  • Previous Protheus - Names and Descriptions of TOTVS ERP tables

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.