1

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:

  1. Whats the reason behind this Error ?
  2. Will UPDATETEXT command effect logging in future? because we are thinking to use CDC for auditing change.
0

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.
  • 1
    What I have done is, I just created a temp table inserted the text value in it, applied all modifications or updates on text column in temp table and then updated the real actual table with simple update statement. – user164767 Jan 11 at 8:05
  • That sounds like a good solution! Did not think of that one – Randi Vertongen Jan 11 at 8:16
  • 1
    and yes we are working to remove UPDATETEXT command from our application code and it requires time till that's a reasonable solution. – user164767 Jan 11 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.