I don't get it. I have the following query :
Insert Into FinancialSQL.DBO.Treatments (TransactionID, TreatmentDate, Account, Pro, Room, Code, Fee) Values (219785, 45166, 9089, 15, 5, '01120', 46.75)
Although the Code column in the table is a varchar(5), I get the following error: Error: The conversion of the varchar value '01120' overflowed an INT1 column. Use a larger integer column.
What is the darn thing trying to tell me?
Thanks!
Triggers?
Nope, no triggers on that table.
What is an INT1 column? In SQL Server, there are INT and BIGINT. And that doesn't look like a SQL Server error.
SELECT CAST('01120' AS int)
-----------
1120
(1 row affected)
I know, I know. So, no, I haven't been imbibing and I'm not imagining things. Not the first time that I get an error from SQL Server with a mention of INT1 and I have no idea what it's talking about. This query runs fine 99.99999999999% of the time.
I think .net sometimes returns INT1 for tinyint. Your problem is probably something simple. If you want help post consumable test data and the code.
You posted this exact same issue here: Weird insert error
The error can be reproduced by casting the string to tinyint (INT1). Somewhere there is an attempt to convert the value - and as was stated before, it has to be somewhere in the code or definition of the table. It would help if you posted the full definition of the table - including any computed columns and triggers.
You're right ! You're more awake than I am. I thought I had tried to address this before but wasn't sure anymore.
So bear with me here, how would I go about getting a full table definition summary? There are no computed columns or triggers on this table.
Right-click on the table in Object Explorer and script as create. You can also open the Keys, Constraints, Triggers and Indexes folders to see what is currently defined for that table.
So here's the table definition:
USE [FinancialSQL]
GO
/****** Object: Table [dbo].[Treatments] Script Date: 2023-08-31 16:48:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Treatments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionID] [int] NULL,
[TreatmentDate] [int] NULL,
[TransactionTime] [int] NULL,
[Account] [int] NULL,
[Invoice] [int] NULL,
[Pro] [smallint] NULL,
[Room] [smallint] NULL,
[Code] [varchar](5) NULL,
[Tooth] [tinyint] NULL,
[Surface] [varchar](6) NULL,
[DentalFee] [float] NULL,
[LabFee] [float] NULL,
[TreatmentCategory] [smallint] NULL,
[Hidden] [tinyint] NULL,
[EntryType] [tinyint] NULL,
[TreatmentDescription] [varchar](max) NULL,
[IndependentAssociate] [smallint] NULL,
[MacroID] [smallint] NULL,
[Valid] [tinyint] NULL,
[OdontogramCode] [varchar](20) NULL,
[LabCode1] [varchar](5) NULL,
[LabFee1] [float] NULL,
[LabCode2] [varchar](5) NULL,
[LabFee2] [float] NULL,
[AssociateID] [smallint] NULL,
[Practice] [smallint] NULL,
[StdRate] [float] NULL,
[Counts] [smallint] NULL,
[Reference] [int] NULL,
[HIPLine] [tinyint] NULL,
CONSTRAINT [aaaaaTreatments_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [TransactionID]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((-1)) FOR [TreatmentDate]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((-1)) FOR [TransactionTime]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [Account]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [Invoice]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [Pro]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [Room]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [Tooth]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [DentalFee]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [LabFee]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [TreatmentCategory]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [Hidden]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [EntryType]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((1)) FOR [IndependentAssociate]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [MacroID]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((1)) FOR [Valid]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [LabFee1]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [LabFee2]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((0)) FOR [AssociateID]
GO
ALTER TABLE [dbo].[Treatments] ADD DEFAULT ((1)) FOR [Practice]
GO
Keys
USE [FinancialSQL]
GO
/****** Object: Index [aaaaaTreatments_PK] Script Date: 2023-08-31 16:50:52 ******/
ALTER TABLE [dbo].[Treatments] ADD CONSTRAINT [aaaaaTreatments_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
Apart from the error on the column name "Fee" (which I replaced with LablFee1), the error you mentioned is not repeated. The record is added successfully.
Something different happens to you.
True. In my original post, for the sake of making the post more intelligible for viewers, I simplified the query and changed a few names. I didn't originally expect to show all the table details. In the end, it still doesn't explain why it wants to perform a CAST to a byte, if that is indeed what it means by INT1.
It is attempting to cast to a 'tinyint' - which is INT1. It is reproducible - and I showed how to reproduce it. Unless you are hitting a bug in SQL Server - then something in your code is causing the problem.
The thing I see here is that the column that follows the 'Code' column is 'Tooth' which is defined as ... tinyint. That leads me to believe that your code is trying to insert the value into the 'Code' column but it is actually attempting to insert into the 'Tooth' column which will cause this error.
Best guess - the code is attempting to insert by position and somehow the insert gets shifted.
Thing is, the code is straightforward, clear as daylight and works 99.999% of the time. I just can't swallow your potentially valid "shifted insert" hypothesis because then that would fundamentally mean that I can no longer have any confidence in SQL Server. I didn't delegate the Insert order to the engine so I cannot accept that SQL Server would swap elements in the execution order.
You can use an extended event to capture the error and the SQL it was trying to run. Start an extended event session and capture the error_reported event.
In the configure tab, capture the sql_text.
Add a filter for severity >= 16
Write the events to a file. That will likely get the SQL that you are running. That should help with this.
Thanks Graz. Hopefully, this will provide insight if it ever happens again.
BTW, I noticed that the DB is running at compatibility level 100 (SQL Server 2008) on a SQL Server 2019 engine. I remember that this was done because there was an issue arising when using SQL Server 2014 which may no longer exist with SQL Server 2019. Could this possibly contribute to having something "lost in translation"?
I think it is very unlikely. But I also think you are better to be on the highest level of compatibility if you can.
