on 11-10-2015 4:38 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.