Error on CDC enabled Table I am trying to create an SP on a CDC enabled database. SP contain an update statement for CDC enabled table using UPDATETEXT command but CREATE PROCEDURE statement has generated an ERROR Message.


  "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."


I have followed the instructions on a post that disable CDC for the table for which sp has command UPDATETEXT and then create SP and after sp creation re- enable CDC for that table

Question: 


Whats the reason behind this Error ?  
Will UPDATETEXT command effect logging in future? because we are thinking to use CDC for auditing change.
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 &amp; 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.