INF: UPDATETEXT Must Be Run in the Published Database

MORE INFORMATION

The following scenario illustrates the problem:

When you connect to the "master" database and perform a transaction that has an UPDATETEXT command involving a table in the "pubs" database, the transaction will not be replicated. If you include a "USE pubs" command at the beginning of the same transaction, the transaction will be replicated.

The logreader reports success with the following message:

The distribution process will not be affected. Subsequent data modification statements will be replicated, and there will not be any operational discontinuity unless these operations refer to the data that was not replicated.

The following code section explains the problem behavior (the section assumes that the "pub_info" table in "pubs" is published):

use master
go
begin transaction
declare  @textptr1 varbinary(16)
select @textptr1=TEXTPTR(pr_info)
from pubs..pub_info where pub_id=<value>
updatetext pubs..pub_info.pr_info @textptr1 NULL 0 with log 'Text..  '
commit tran
go
				

This will update the text value in pr_info column but will not be replicated. The updating process has to be in the pubs database during the updatetext call, which can be done by using "USE pubs" explicitly. In the case of a cross-database query, you can achieve this goal by using a dummy update inside the transaction that will set the process to be in the "pubs" database. The correct means of using UPDATETEXT for such cases would look as follows:

1. if possible, explicitly set the database
   

     use pubs
     go
     begin transaction
   
   						

2. if inside a cross-database query, use the dummy update
3. if "use <database>" can be used, this update is not required
   

     update pubs..pub_info set pub_id=pub_id where pub_id=<value>
     declare  @textptr1 varbinary(16)
     select @textptr1=TEXTPTR(pr_info)
     from pubs..pub_info where pub_id=<value>
     updatetext pubs..pub_info.pr_info @textptr1 NULL 0 with log
     'Inserted..  '
     commit tran
     go