Sql-server – Error on CDC enabled Table

Whats the reason behind this Error ?

It is in the error message,

"UPDATETEXT is not allowed because ... or tracked by Change Data Capture.

You cannot use it on columns tracked by CDC.

Testing

Create table & insert sample data

CREATE TABLE dbo.tablea
(id int identity(1,1) PRIMARY KEY NOT NULL, UpdateTextTest TEXT)

INSERT INTO Dbo.tablea(UpdateTextTest)
VALUES('bBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB')

Test updatetext without CDC

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM dbo.tablea

UPDATETEXT   tablea.UpdateTextTest  @ptrval 8 1 'c';

This runs fine

Result

id  UpdateTextTest
1   bBbBbBbBcBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB

The 9th value was changed to 'C'.

Enable CDC on the db and table

use my_test
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'tablea',  
@role_name     = NULL ,  
@filegroup_name = N'PRIMARY',  
@supports_net_changes = 1;
GO  

Try to run the same query

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM dbo.tablea

UPDATETEXT   tablea.UpdateTextTest  @ptrval 8 1 'c';

The same error

Msg 7137, Level 16, State 1, Line 41
UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot or is being replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS) or tracked by Change Data Capture.

By the way, updatetext might be removed in future versions, and TEXT,NTEXT and IMAGE datatypes are also deprecated

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use the large-value data types and the .WRITE clause of the UPDATE statement instead.

Source

---

What could you do?

I would advise to try and change the column to either nvarchar(max), varchar(max) or varbinary(max) depending on the deprecated datatype used, and then using .write .

Testing with text:

UPDATE tablea
SET UpdateTextTest .WRITE (N'c',9,1)

Msg 258, Level 15, State 1, Line 44
Cannot call methods on text.

As it is now, I cannot use .Write since the datatype is text.

Changing the datatype and testing the query again, with CDC enabled. Alter table tablea alter column UpdateTextTest varchar(MAX)

UPDATE tablea
SET UpdateTextTest .WRITE (N'c',9,1)  

Result:

id  UpdateTextTest
1   bBbBbBbBccbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB

the 10th value was also changed to 'c'.

---

Extra info

Readtext works with cdc

DECLARE @ptrval varbinary(16);  
SELECT @ptrval = TEXTPTR(UpdateTextTest)   
   FROM tablea pr 

READTEXT tablea.UpdateTextTest @ptrval 1 1;  

Result:

 UpdateTextTest
B

Writetext does not work with CDC

DECLARE @ptrval binary(16);  
SELECT @ptrval = TEXTPTR(UpdateTextTest)   
FROM tablea pr
WRITETEXT tablea.UpdateTextTest @ptrval 'DDDDDD';  

Error:

Msg 7138, Level 16, State 1, Line 92
The WRITETEXT statement is not allowed because the column is being replicated with Data Transformation Services (DTS) or tracked by Change Data Capture.