[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to avoid the error "String or binary data would be truncated. The statement has been terminated".

Posted on 2009-04-15
Medium Priority
Last Modified: 2012-06-27
I am developing an Access application using Access as the front end and SQL Server as the back end database.

I have created the following stored procedure in the "Code:" section that follows.

When I run the stored procedure, I get the following error message:

Server: Msg 8152, Level 16, State 9, Procedure GetLocations, Line 4
String or binary data would be truncated.
The statement has been terminated.

Do you have an idea of where the error occurs among all the fields displayed ?

I imagine the error is caused by trying to insert a string with more characters than the column can maximally accommodate.

CREATE PROCEDURE [dbo].[MS_GetLocations] 
-- Execute query for finding FSG location data info
SELECT       LocationID = right('00000' + CONVERT(varchar, dbo.tblLocation.LocationID), 6), 
	     dbo.tblLocation.Description, dbo.tblLocation.Address1, dbo.tblLocation.Address2, dbo.tblLocation.City, 
             dbo.tlkpStates.Abbreviation AS State, dbo.tblLocation.Zip, 
             [Cost Center] = 
                 WHEN LEN(dbo.tlkpOrgLevelDescription.OrgLevelName) > 6 THEN dbo.tlkpOrgLevelDescription.OrgLevelName
                 ELSE right('00000' + CONVERT(varchar, dbo.tlkpOrgLevelDescription.OrgLevelName), 6) 
             dbo.tlkpStatus.Status AS [Property Status], dbo.tblSpace.LeasedSpace, tlkpStatus_1.Status AS [Lease Status],  
	     convert(char(10), dbo.tblSpace.EndDate, 103) as EndDate
FROM         dbo.tblLocation INNER JOIN
                      dbo.tlkpStates ON dbo.tblLocation.StateID = dbo.tlkpStates.ID INNER JOIN
                      dbo.tblSpace ON dbo.tblLocation.LocationID = dbo.tblSpace.LocationID INNER JOIN
                      dbo.tlkpSpaceAllocationTerm ON dbo.tblSpace.SpaceID = dbo.tlkpSpaceAllocationTerm.SpaceID INNER JOIN
                      dbo.tblAllocation ON dbo.tlkpSpaceAllocationTerm.tlkpSpaceAllocationTermID = dbo.tblAllocation.tlkpSpaceAllocationTermID INNER JOIN
                      dbo.tlkpOrgLevelDescription ON dbo.tblAllocation.OrgLevelID = dbo.tlkpOrgLevelDescription.OrgLevelID INNER JOIN
                      dbo.tlkpStatus ON dbo.tblLocation.StatusID = dbo.tlkpStatus.StatusID INNER JOIN
                      dbo.tlkpStatus tlkpStatus_1 ON dbo.tblSpace.IsActive = tlkpStatus_1.StatusID
WHERE     (dbo.tblAllocation.RSF > 0)
GROUP BY dbo.tblLocation.LocationID, dbo.tblLocation.Description, dbo.tblLocation.Address1, dbo.tblLocation.Address2, dbo.tblLocation.City, 
                      dbo.tlkpStates.Abbreviation, dbo.tblLocation.Zip, dbo.tlkpOrgLevelDescription.OrgLevelName, dbo.tlkpStatus.Status, dbo.tblSpace.LeasedSpace, 
                      tlkpStatus_1.Status, dbo.tblSpace.EndDate

Open in new window

Question by:zimmer9
LVL 15

Expert Comment

ID: 24151499
That error is typically caused by trying to insert a data string that is longer than the field size for that column.  Check the length of the data being saved and compare that with the field sizes being used.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 24151514
>Do you have an idea of where the error occurs among all the fields displayed ?
unfortunately, the error does not give the information, so you have 2 approaches:
* positive check (recommended method)
  check each destination fields' data type and size, and put a LEFT() function on the originating fields where the originating fields data type/size is larger, or change the source/destination's field size accordingly to match on both sides

* negative check (might be quicker, but leaves data sizes unchecked):
  comment out all the fields until the error disappears, then you know which field is the culprit.
  once the change (same as above) is done, uncomment field by field to confirm the error does not occur on the other fields neiter

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question