October 19, 2010 at 4:04 am
When i am executing the below procedure i got the message
String or binary data would be truncated.
EXEC FASST_DocCheck_TableRefresh 'ProxyDocCheck',7
Its very urgent, can any one please reply me immidiately?
Thanks a lot
create PROCEDURE [dbo].[FASST_DocCheck_TableRefresh]
@TableNamePrefix nvarchar(49),
@KeyDocCheck smallint = 1
AS
BEGIN
SET NOCOUNT ON;
-- Some locals
DECLARE @ActiveTableName nvarchar(50);
DECLARE @InActiveTableName nvarchar(50);
DECLARE @sql nvarchar(200);
DECLARE @Parameters nvarchar(100);
DECLARE @LockResult int;
DECLARE @RefreshStart DateTime;
DECLARE @Rows int;
DECLARE @FullBuildProc nvarchar(50);
DECLARE @IncrementalBuildProc nvarchar(50);
-- Determine what the active, inactive tables are
SELECT @ActiveTableName = ActiveTableName,
@InActiveTableName = InActiveTableName,
@FullBuildProc = FullBuildProcName,
@IncrementalBuildProc = IncrementalBuildProcName
FROM ActiveTables
WHERE TableNamePrefix = @TableNamePrefix
-- Get number of rows in table now
SET @sql = 'SELECT @Rows = Count(1) FROM ' + @InActiveTableName;
SET @Parameters = '@Rows int out';
EXEC sp_Executesql @sql, @Parameters, @Rows out;
IF @Rows = 0
BEGIN
SELECT 'Full population';
-- Run full populatioon query
EXEC @FullBuildProc null, @TableNamePrefix, @KeyDocCheck;
-- Switch tables
EXEC FASST_SwitchTables @TableNamePrefix;
-- Copy to other table
SET @sql =
'TRUNCATE TABLE ' + @ActiveTableName; -- note that we switched, so the @active is now the inactive if we queried again
EXEC sp_Executesql @sql, N'';
-- Fill the now inactive table from the active
SET @sql = 'INSERT INTO ' + @ActiveTableName + ' SELECT * FROM ' + @InActiveTableName;
EXEC sp_Executesql @sql, N'';
END
ELSE
BEGIN
SELECT 'Incremental population';
-- Run full incremental query
EXEC @IncrementalBuildProc @TableNamePrefix, @KeyDocCheck;
END
END
October 19, 2010 at 4:58 am
The message 'Msg 8152, Level 16, State 14, Line 3 String or binary data would be truncated.
The statement has been terminated." is caused by the value attempting to be inserted/updated into a column exceeds the size of the column.
So if the column is defined as char(1) and you attempt to insert 'abc", which has a length of 3, error 8152 will result.
There is an open request to MS to report the name of the column whose capacity is being exceeded.
Here is the SQL to cause the error message:
use tempdb
go
if object_id( 'tempdb..#DataTruncated') is not null
drop table #DataTruncated
go
create table #DataTruncated
(firstColumnchar(1)not null
)
go
print 'no error'
insert into #DataTruncated
(firstColumn )
values ( 'a' )
go
print 'Cause "String or binary data would be truncated." error message.'
insert into #DataTruncated
(firstColumn)
values ( '123')
go
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply