Microsoft KB Archive/154048

= INF: UPDATETEXT Must Be Run in the Published Database =

Article ID: 154048

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q154048



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 " 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= 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 " can be used, this update is not required

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



Additional query words: text replication

Keywords: kbbug KB154048

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.