SQL Server Performance

URGENT|Data Truncation | Migration from Lotus Notes to SQL Server 2005

Discussion in 'SQL Server 2005 General Developer Questions' started by SQLWorld, Nov 11, 2009.

  1. SQLWorld New Member

    Hi All,

    We are migrating data from Lotus Notes to SQL Server 2005 for PHPBB application.
    The issue we are facing while updating a ntext column with nVarchar(max) data we receive data truncation error. Both nvarchar(max) and ntext can accommodate 2 GB of data. Post_text is a ntext column.

    declare @Pposter as varchar(50) -- name of the poster derived from user table profile
    declare @Quotetxt as nvarchar(max)
    DECLARE @ptr varbinary(16)
    declare @Ptext as nvarchar(max)
    declare @parent_id as varchar(100)
    declare @koid as varchar(20) -- value is 'A321GY'
    declare @rBody as varchar(max)

    Msg 8152, Level 16, State 14, Procedure Phpbb_migrate_data2, Line 162
    String or binary data would be truncated.
    The statement has been terminated.


    This happens for 50/60 rows out of 1200 rows. Any pointer to this will help us.

    Statement to update column:

    If @parent_id <> @topicID
    begin
    Set @Ptext =( Select body from Reply_Temp_KX29 where ReplyUNID = @parent_id)
    Set @Pposter=(Select username from phpbb_users where KOID =
    (Select KOID from Reply_Temp_KX29 where ReplyUNID = @parent_id))
    Update phpbb_posts set
    --[quote="admin":3efeqo4w]Hi[/c3efeqo4w]My reply
    bbcode_bitfield ='gA==',
    bbcode_uid = @koid,
    post_text = '[quote="'+@Pposter+'"'+';:'+@koid+']'+ @Ptext+'[/quote:'+@koid+']'+
    @rBody)
    where post_id =@pid

    end


    Even I do try the following syntax to update ntext column, but I receive the same truncation error for 50/60 rows.

    SELECT @ptr = TEXTPTR(phpbb_posts.post_text)
    FROM phpbb_posts
    WHERE post_id = @pid

    If @parent_id <> @topicID
    begin
    Set @Ptext =( Select body from Reply_Temp_KX29 where ReplyUNID = @parent_id)
    Set @Pposter=(Select username from phpbb_users where KOID =
    (Select KOID from Reply_Temp_KX29 where ReplyUNID = @parent_id))
    Update phpbb_posts set
    --[quote="admin":3efeqo4w]Hi[/c3efeqo4w]My reply
    bbcode_bitfield ='gA==',
    bbcode_uid = @koid
    where post_id =@pid
    set @Quotetxt ='[quote="'+@Pposter+'"'+';:'+@koid+']'+@Ptext+'[/quote:'+@koid+']'+@rBody
    WRITETEXT phpbb_posts.post_text @ptr @Quotetxt

    end


    Thanks for your help.
  2. arunyadav Member

    Data truncation error comes when the target column cannot accomodate the value being inserted or, updated... Foe example... Col1 varchar (2) null
    Insert into table (Clo1)
    values ('Sam')
    This will result in the same error...
  3. satya Moderator

Share This Page