cancel
Showing results for 
Search instead for 
Did you mean: 

Data exception - string data right truncated error

Former Member
0 Kudos

Hi Team,

I have this weird error on my replication environment when applying a DDL change to primary server being replicated to MSA and Warm Standby.

Can some explain why I receive this error?

The command was

IF EXISTS

    (

    SELECT 1

    FROM sysobjects, syscolumns

    WHERE

        sysobjects.id = syscolumns.id                                                   

        AND sysobjects.name = 'SequentialValues'

        AND syscolumns.name = 'description'

        and syscolumns.status <> 0

     )

EXEC ('ALTER TABLE SequentialValues MODIFY description NOT NULL')

go

This executed successfully on the primary with no warning but brought the DSI down on the warm standby and MSA

Message from server: Message: 9502, State 9, Severity 16 -- 'Data exception - string data right truncated'

The DSI thread for database 'WSBSRV.DB' is being shutdown. DSI received data server error #9502 which is mapped to STOP_REPLICATION. See logged data server errors for more information. The data server error was caused by output command #0 mapped from input command #0 of the failed transaction.

I. 2015/10/29 14:44:30. The DSI thread for database 'WSBSRV.DB' is shutdown.

E. 2015/10/29 14:48:21. ERROR #1028 DSI EXEC(466(1) MSASRV.DB) - dsiqmint.c(4722)

        Message from server: Message: 9502, State 9, Severity 16 -- 'Data exception - string data right truncated'

The script first does the following

1. first add the field

ALTER TABLE SequentialValues ADD description varchar(100) NULL

2. update the column to make sure non of the data is null

3. then alter the table to make it not null

When I look at the primary table table the largest data length in that column is 70, which is well below the 100 defined on the table

SELECT max(char_length(description)) FROM dbo.SequentialValues

I was able to resume the DSI only after manually applying the same command on the WSB and MSA database.

On resumption of the DSI the following warning message was displayed but the transactionwas able to continue

. 2015/10/29 16:24:33. The DSI thread for database 'WSBSRV.DB' is started.

I. 2015/10/29 16:24:33. Message from server: Message: 13925, State 1, Severity 10 -- 'Warning: ALTER TABLE operation did not affect column 'description'.'.

I. 2015/10/29 16:24:33. Message from server: Message: 13905, State 1, Severity 10 -- 'Warning: no columns to drop, add or modify. ALTER TABLE 'SequentialValues' was aborted.'.

I. 2015/10/29 16:24:33. Database 'WSBSRV.DB' returns messages that are mapped to IGNORE or WARN by error action mapping. See logged data server messages for more information.

I. 2015/10/29 16:26:00. The DSI thread for database 'MSASRV.DB' is started.

I. 2015/10/29 16:26:00. Message from server: Message: 13925, State 1, Severity 10 -- 'Warning: ALTER TABLE operation did not affect column 'description'.'.

I. 2015/10/29 16:26:00. Message from server: Message: 13905, State 1, Severity 10 -- 'Warning: no columns to drop, add or modify. ALTER TABLE 'SequentialValues' was aborted.'.

I. 2015/10/29 16:26:00. Database 'MSASRV.DB' returns messages that are mapped to IGNORE or WARN by error action mapping. See logged data server messages for more information.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi , Just adding to this long old post . I am getting a similar replication thread down issue once I put an alter statement like this

eg   alter table ash_1  add rate_vol_test1 char(1) DEFAULT "RSRTEST" NOT NULL “



E. 2016/09/21 20:05:06. ERROR #1028 DSI EXEC(1293(1) TACDEV2.mts_metals_feed) - dsiqmint.c(4719)

Message from server: Message: 9502, State 28, Severity 16 -- 'Data exception - string data right truncated

though on primary side we didnt got any errors but while replicating destination side goes down


Primary : 

alter table ash_1

add rate_vol_test1 char(1) DEFAULT "RSRTEST" NOT NULL

go

(9578 rows affected)

rep version :  Replication Server/15.7.1/EBF 21866 SP120 rs1571sp120/Linux AMD64/Linux 2.6.18-128.el5 x86_64/1/DEBUG64/Sun Dec  8 15:09:33 2013

ASE version both sides :  Adaptive Server Enterprise/15.7/EBF 25755 SMP SP136 HF1/P/x86_64/Enterprise Linux/ase157sp136x/3963/64-bit/FBO/Mon Feb 15 20:28:33 2016                    

terry_penna
Employee
Employee
0 Kudos

Hi Ashish

Mark is correct that a new thread should be created for this topic to keep issues and questions separate to reduce any confusion on what the answers are being given to the OP.

-Regards

Terry Penna

former_member182259
Contributor
0 Kudos

Not sure about current releases, but older RS versions didn't like EXEC_IMMEDIATE......not sure why you are using it above as there is no reason to.    I would try it without the EXEC() function - just call the ALTER TABLE natively and see what happens.    I would also check the contents to see if there are empty strings in the target or null values.

Former Member
0 Kudos

Hi Jeff,

There are certain scenario that requires that we use execute immediate when altering tables and making the script re-runnable. I think I did mention these circumstances to you at ISUG Tech  last year but I will conduct some tests to see if I could reproduce those situations.

Cheers

Former Member
0 Kudos

Hi Jeff,

I had some time to look into the issue regarding why we had to have exec('') in some of our code.

I created two scripts one that creates a  new table, both checking if the tables exists before creating it.

Using EXEC

-----------------

cat cr_tb_with_exec.sql

use Research

go

IF OBJECT_ID ('TestExec') IS NULL BEGIN exec('

create table TestExec (

        UserID                      int                              not null  ,

        FirstName                   varchar(30)                       not null  ,

        LastName                    varchar(30)                       not null  ,

        CONSTRAINT PK_TestExec_UserID PRIMARY KEY CLUSTERED ( UserID )

)

lock datapages

')

END

Go

isql -Usa -S DEV -i test_exec3.sql -o test_exec3.sql.log

execute multiple times without error

Without EXEC

----------------------

cr_tb_no_exec.sql

use Research

go

IF OBJECT_ID ('TestExec') IS NULL BEGIN

create table TestExec (

        UserID                      int                              not null  ,

        FirstName                   varchar(30)                       not null  ,

        LastName                    varchar(30)                       not null  ,

        CONSTRAINT PK_TestExec_UserID PRIMARY KEY CLUSTERED ( UserID )

)

lock datapages

END

Go

isql -Usa -SDEV -i cr_tb_no_exec.sql -o cr_tb_no_exec.sql.log

first time no error

re-executing causes an error - complain table already exists

cat cr_tb_no_exec.sql.log

Msg 2714, Level 16, State 1:

Server 'DEVt', Line 2:

There is already an object named 'TestExec' in the database.

I have tested some cases such as creating indexes/FK where we don't have an issue but as a general rule the developers have opted for using exec when creating or altering objects,

This is the reason we have used exec('') when alter objects in the code, due to the fact they have to make the code re-executable as part of the development life cycle

Regards