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

Posted on 2009-04-15
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

    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 142

    Accepted Solution

    >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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now