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

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] 
AS
-- 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] = 
             CASE 
                 WHEN LEN(dbo.tlkpOrgLevelDescription.OrgLevelName) > 6 THEN dbo.tlkpOrgLevelDescription.OrgLevelName
                 ELSE right('00000' + CONVERT(varchar, dbo.tlkpOrgLevelDescription.OrgLevelName), 6) 
             END, 
             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
GO

Select allOpen in new window