none
Error Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated.

    Question

  • USE [abc]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_abc1]    Script Date: 05/19/2011 10:38:26 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    --EXEC abc.dbo.sp_abc1 100, 1,'t.[Summary*]','1','1','1','1','1','1','1','1'
    ALTER PROCEDURE [dbo].[sp_abc1] 
    (
    @insert_days INT ,
    @DataSource_ID int,
    @Location_Name VARCHAR(100),
    @Location_Type VARCHAR(100),
    @Location_Address1 VARCHAR(100),
    @Location_Address2 VARCHAR(100),
    @Location_ZIP varchar(100), 
    @Location_Country VARCHAR(100),
    @Location_City VARCHAR(100),
    @Location_State VARCHAR(100), 
    @Active BIT
    )
    AS 
        BEGIN
            DECLARE @SQL NVARCHAR(MAX)
            DECLARE @SchemaName SYSNAME
            DECLARE @TableName SYSNAME
            DECLARE @DatabaseName SYSNAME
            DECLARE @BR CHAR(2)
            SET @BR = CHAR(13) + CHAR(10)
            
            SELECT  @SchemaName = Source_Schema ,
                    @TableName = Source_Table ,
                    @DatabaseName = Source_Database
            FROM    xyz.dbo.table1
            WHERE   ID = @Datasource_ID 
            
            SET @SQL = 'INSERT INTO ' + @DatabaseName + '.' + @SchemaName + '.'
                + @TableName + '_loctemp (' + @BR + '[Location_Name],' 
                + @BR + '[Location_Type],' + @BR + '[Location_Address1],' + @BR + '[Location_Address2],'
                + @BR + '[Location_ZIP],' + @BR + '[Location_Country],' + @BR + '[Location_City],'
                + @BR + '[Location_State],' + @BR + '[__Active],' + @BR + '[__DataSource_ID],'
                + @BR + '[__Source_Rec_ID]'+ @BR + ')' + @BR
                
            SET @SQL = @SQL + 'SELECT convert(varchar(500),' + @Location_Name + ') AS [Location_Name],' + @BR
                                       + 'convert(varchar(500),' + @Location_Type + ') AS [Location_Type],' + @BR
                                      + 'convert(varchar(500),' + @Location_Address1 + ') AS [Location_Address1],' + @BR
                                      + 'convert(varchar(500),' + @Location_Address2 + ') AS [Location_Address2],' + @BR
                                      + 'convert(varchar(50),' + @Location_ZIP + ') AS [Location_ZIP],' + @BR 
                                       + 'convert(varchar(500),' + @Location_Country + ') AS [Location_Country],' + @BR
                                      + 'convert(varchar(500),' + @Location_City + ') AS [Location_City],' + @BR
                                      + 'convert(varchar(500),' + @Location_State + ') AS [Location_State],' + @BR 
                                       + CAST(@Active AS NVARCHAR(10)) + ' AS [__Active] ,' + @BR
                                       + CAST(@Datasource_ID AS NVARCHAR(10)) + ' AS [__DataSource_ID],' + @BR 
                                       + '__Rec_ID AS __Source_Rec_ID' + @BR
                                       + 'FROM ' + @DatabaseName + '.' + @SchemaName + '.' + @TableName + ' t' + @BR                                  
                                       + 'WHERE t.[__INSERT_DATE] > GETDATE() - ' + CAST(@insert_days AS NVARCHAR(10)) + @BR                                   
                                       + 'AND ' + @Location_Name + ' IS NOT NULL' + @BR
                                       + 'AND t.[__Active] = 1' + @BR
            EXEC (@SQL)
    END
            
            Getting error Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. Please help...
            
    The data in the columns are more than 1000 characters and I just need them till 500 character. That's why I have used convert on required columns.
                                        
                                        
                                        

    Thursday, May 19, 2011 4:10 PM

Answers

  • Hello,

    In common this error message raises if you try to insert long values then defined vor a table field, e.g. if you try to insert a varchar(500) into a varchar(200) field.

    And don't use CONVERT to truncate a string; use SUBSTRING(value, 1, 500) instead.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Naomi NModerator Thursday, May 19, 2011 5:04 PM
    • Marked as answer by KJian_ Thursday, May 26, 2011 5:36 AM
    Thursday, May 19, 2011 4:32 PM
  • You can use either substring as mentioned by Olaf. or you can use Left function

     

    Substring

     

    SUBSTRING(value, 1, 500)

     

    Left:

     

    Left(Value,500)


    If this answer is helpful to you .. Please mark as Answer....
    Thursday, May 19, 2011 7:49 PM

All replies

  • Hello,

    In common this error message raises if you try to insert long values then defined vor a table field, e.g. if you try to insert a varchar(500) into a varchar(200) field.

    And don't use CONVERT to truncate a string; use SUBSTRING(value, 1, 500) instead.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    • Proposed as answer by Naomi NModerator Thursday, May 19, 2011 5:04 PM
    • Marked as answer by KJian_ Thursday, May 26, 2011 5:36 AM
    Thursday, May 19, 2011 4:32 PM
  • That's true. But I need to fit just 500 characters in those column. I have to put restriction on those fields not to enter more than 500 character, if it enters just take upto 500 characters not more than that.

    What else I can use to get upto 500 characters in that fields.

    Any other syntax like left(...).

     

    Thursday, May 19, 2011 6:06 PM
  • You can use either substring as mentioned by Olaf. or you can use Left function

     

    Substring

     

    SUBSTRING(value, 1, 500)

     

    Left:

     

    Left(Value,500)


    If this answer is helpful to you .. Please mark as Answer....
    Thursday, May 19, 2011 7:49 PM
  • What else I can use to get upto 500 characters in that fields.

    Any other syntax like left(...).


    As I wrote: SUBSTRING( @YourValue, 1, 500), if truncate the @YourValue from first position with a length of 500.
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Friday, May 20, 2011 6:18 AM
  • Your script inserts data into the table  @TableName + '_loctemp' from  @TableName . Typically both should have same structure ,data types and length per my understanding as it seems to be the temproary replica of the the table. Please compare both the table structures.

    You can use the below script to compare the tables

    http://gallery.technet.microsoft.com/scriptcenter/12b98927-b464-4330-a8f0-e55ad8998a62

     

    Friday, May 20, 2011 8:50 AM