INF: UPDATETEXT Must Be Run in the Published Database

• 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 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

• 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