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

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.

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...