INF: UPDATETEXT Must Be Run in the Published Database

ID: Q154048

The information in this article applies to:

Microsoft SQL Server version 6.5

SUMMARY

An UPDATETEXT transaction will not be replicated if the process is not in the database that contains the updated table, even if it uses the "WITH LOG" option. The transaction must follow a "USE <database>" statement or a dummy update must be done on the table before the transaction can be replicated.

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:

processed 1 replicated transaction containing 0 commands.

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:

if possible, explicitly set the database
```
  use pubs
  go
  begin transaction
 
```

if inside a cross-database query, use the dummy update

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

Additional query words: text replication

Keywords : SSrvProg SSrvRep SSrvTran_SQL kbbug6.50
Version : 6.5
Platform : WINDOWS
Issue type :