Whenever you happen to work with INSERT or UPDATE Transact-Sql queries, either from within Sql Serve Management Studio or through your favorite programming platform like Visual Studio, you’re often likely to stumble across the error message that says:
String or binary data would be truncated…
and your query fails right away.
The message is straight-forward but the actual problem lies in figuring out the problematic field. This becomes cumbersome if your query involves lots of varchar or nvarchar type of fields.
By using a regular method, you have to go through each such field and its corresponding value one by one and many a times you have to refer to the table design to check the length of the fields in question.
There’s one quick trick to figure out the field(s) with overflown values. But first you have to make some preparations, which we’re
going to put forward below.
As an example suppose we have a table “CustomerAccount” whose table schema is defined by the query as below:
CREATE TABLE dbo.CustomerAccount (
id_CustomerAccount INT IDENTITY(1,1) PRIMARY KEY,
— Foreign keys
id_City INT NOT NULL,
id_Country INT NOT NULL,
id_AccountType INT NOT NULL,
CustomerCode VARCHAR(20) NOT NULL, — Unique code like “CUST-00123”
CustomerName NVARCHAR(100) NOT NULL,
ContactPerson NVARCHAR(100) NULL,
ContactEmail VARCHAR(100) NULL,
ContactPhone VARCHAR(20) NULL,
AlternatePhone VARCHAR(20) NULL,
FaxNumber VARCHAR(20) NULL,
Website VARCHAR(100) NULL,
TaxNumber VARCHAR(50) NULL, — e.g., VAT ID
BillingAddressLine1 NVARCHAR(200) NULL,
BillingAddressLine2 NVARCHAR(200) NULL,
PostalCode VARCHAR(20) NULL,
AccountStatus VARCHAR(20) NOT NULL DEFAULT ‘Active’,
IndustryType NVARCHAR(100) NULL,
Notes NVARCHAR(MAX) NULL,
DateCreated DATETIME NOT NULL DEFAULT GETDATE(),
IsPreferred BIT NOT NULL DEFAULT 0
);
First you need to quickly get the table schema of the table in question (in our case it is CustomerAccount table):
Run this query in your query editor from within Sql Server Management Studio. You only need to make one change in the query, that is, the table name, on the last line. Don’t forget to check “Result to Text” icon from within Sql Management Studio query editor. This is because you have to copy/paste it as a part of the prompt to your generative AI assistant.
SELECT
c.name AS [Column Name],
t.name AS [Data Type],
c.max_length AS [Length]
FROM
sys.columns c
JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(‘dbo.CustomerAccount ‘);
Keep this query on some safe place which you can quickly access from anywhere, because you’ll want to use it off and on.
The query will get you the result like the following:
Now open your favorit AI assistant like ChatGPT and make a prompt like the following:
Against this sql-insert/update query
INSERT INTO dbo.CustomerAccount (
id_City,
id_Country,
id_AccountType,
CustomerCode,
CustomerName,
ContactPerson,
ContactEmail,
ContactPhone,
AlternatePhone,
FaxNumber,
Website,
TaxNumber,
BillingAddressLine1,
BillingAddressLine2,
PostalCode,
AccountStatus,
IndustryType,
Notes,
DateCreated,
IsPreferred
)
VALUES (
101, — id_City
1, — id_Country
2, — id_AccountType
‘CUST-000999’, — Within limit (20)
— Over 100 characters
N’VeryLongCustomerNameIncorporatedWithGlobalPartnershipHoldingsAndSubsidiariesAcrossMultipleRegionsAndMarketsBeyondLimit’,
— Over 100 characters
N’Mr. Alexander Maximilian Constantine de la Vega III, Global Enterprise Strategic Director of Operations and Planning’,
— Over 100 characters
‘alexander.delavega.with.an.extremely.long.email@verylongcompanynamedomainforsimulationpurposesonlyexample.com’,
‘1234567890’,
‘0987654321’,
‘1122334455’,
— Over 100 characters
‘https://www.thisisaverylongurlfordemonstrationpurposesthatshouldbreakthevarchar100limitationonwebsitefield.com’,
‘VAT-9988776655’,
— Over 200 characters
N’Unit #45B, 9th Floor, Hyper Tower Complex, International Trade Business District, Phase IV, Metropolitan Business Bay, Industrial Estate, Sector Z, National Highway 12 Extension’,
NULL,
‘54321’,
‘Active’,
— Over 100 characters
N’Multi-National Technology Solutions and Integrated Infrastructure for Cross-Platform and Hybrid Cloud Environments’,
N’Notes here’,
GETDATE(),
0
);
for this table
My Sql Server Query Editor says “String or binary data would be truncated.” Please focus on char, varchar and nvarchar fields only and write me field by field that which data values are ok and which ones exceed their respective field length.
and here’s the response from my ChatGPT:
Here’s a field-by-field analysis of your INSERT statement focusing only on varchar and nvarchar fields and checking whether the provided data exceeds the maximum allowed length based on your table schema.
? Within Limit
| Field | Data Type (Length) | Supplied Value | Status |
|---|---|---|---|
CustomerCode | varchar(20) | 'CUST-000999' (11 chars) | ? OK |
CustomerName | nvarchar(200) | N'VeryLongCustomerNameIncorporatedWithGlobalPartnershipHoldingsAndSubsidiariesAcrossMultipleRegionsAndMarketsBeyondLimit' (133 chars) | ? OK |
ContactPhone | varchar(20) | '1234567890' (10 chars) | ? OK |
AlternatePhone | varchar(20) | '0987654321' (10 chars) | ? OK |
FaxNumber | varchar(20) | '1122334455' (10 chars) | ? OK |
TaxNumber | varchar(50) | 'VAT-9988776655' (14 chars) | ? OK |
BillingAddressLine1 | nvarchar(400) | Long address (182 chars) | ? OK |
BillingAddressLine2 | nvarchar(400) | NULL | ? OK |
PostalCode | varchar(20) | '54321' (5 chars) | ? OK |
AccountStatus | varchar(20) | 'Active' (6 chars) | ? OK |
Notes | nvarchar(MAX) | 'Notes here' (11 chars) | ? OK |
? Exceeds Length
| Field | Data Type (Length) | Supplied Value | Length | Limit | Status |
|---|---|---|---|---|---|
ContactPerson | nvarchar(200) | N'Mr. Alexander Maximilian Constantine de la Vega III, Global Enterprise Strategic Director of Operations and Planning' | 127 chars | ? OK | |
ContactEmail | varchar(100) | 'alexander.delavega.with.an.extremely.long.email@verylongcompanynamedomainforsimulationpurposesonlyexample.com' | 114 chars | ? Too long | |
Website | varchar(100) | 'https://www.thisisaverylongurlfordemonstrationpurposesthatshouldbreakthevarchar100limitationonwebsitefield.com' | 129 chars | ? Too long | |
IndustryType | nvarchar(200) | N'Multi-National Technology Solutions and Integrated Infrastructure for Cross-Platform and Hybrid Cloud Environments' | 127 chars | ? OK |
Summary:
| Field Name | Status | Action Required |
|---|---|---|
ContactEmail | ? Too Long | Trim to = 100 characters |
Website | ? Too Long | Trim to = 100 characters |
All other fields with varchar or nvarchar types are within allowed limits.
The tables copy/pasted here look to be somewhat cluttered but it should be ok because you may only be interested in those column values which have exceeded their defined limits and they’re not likely to be too many.
Anyways, while reading this post you might took some time but while executing this tip, it will take pretty short time every time you’re going to resolve this issue because:
- You already have the faulty INSERT/UPDATE query at hand.
- You can pull in the following query from your digital stash and execute it in a few seconds, if you put it in a quickly accessible place. You’ll later use it as a part of your prompt to your AI assistant.
SELECT
c.name AS [Column Name],
t.name AS [Data Type],
c.max_length AS [Length]
FROM
sys.columns c
JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(‘dbo.CustomerAccount ‘);
Now you’re all set to build the prompt for your favorite Generative AI assistant to get the ‘damn’ fields values, as we did above.
Happy Troubleshooting, cheers!!!
You may also be interested in the following articles:
T-SQL – REPLICATE() Function: Formatting and Masking Techniques
https://www.itprecinct.com/tips-and-tricks/t-sql-trick-use-recursive-cte-to-get-hierarchical-data
T-SQL Trick: Use CTE to Render Dates In The Specified Date Range
T-SQL Technique: Get Running Total Right From The Query
SQL Technique: Users Who Consecutively Logged In For Multiple Times
Concatenating Rows in Sql Query
