November 12, 2009 at 12:27 am
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
--
Hi[/c3efeqo4w]My reply
bbcode_bitfield ='gA==',
bbcode_uid = @koid,
post_text = '
'+ @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
--
Hi[/c3efeqo4w]My reply
bbcode_bitfield ='gA==',
bbcode_uid = @koid
where post_id =@pid
set @Quotetxt ='
'+@Ptext+'[/quote:'+@koid+']'+@rBody
WRITETEXT phpbb_posts.post_text @ptr @Quotetxt
end
Cheers,
Got an idea..share it !!
DBA_Vishal
November 12, 2009 at 3:23 am
I doubt the error is from the column varchar(max) -- ntext conversion.
Can you also confirm error is coming from update statement.
Can you check the column definition of bbcode_bitfield and bbcode_uid and confirm the length of input value is less than the data type.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply