Microsoft KB Archive/275436

= BUG: Distribution Agent Fails When Validating Publication with Stored Procedure Articles =

Article ID: 275436

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q275436



BUG #: 100778 (SQLBUG_70)



SYMPTOMS
When performing replication data validation using sp_publication_validation, the Distribution agent may fail with the following error:

Invalid object name ‘sp_name’

&quot;sp_name&quot; is the name of the publishing stored procedure article.



CAUSE
The sp_publication_validation stored procedure calculates the rowcount or checksum at the Publisher and Subscriber. It calls sp_article_validation, which calls sp_table_validation, to gather the validation information on the specified article. It then posts a validation request as a transaction marked for replication to the publication database's transaction log.

The Logreader agent picks up this transaction and puts it into the distribution database. When the Distribution agent receives this request, it compares the validation information, checksum or rowcount, in the request to the contents of the subscriber table.

Because the &quot;sp_name&quot; is not a table, the sp_table_validation fails at the following statement: insert into #tab_validt1 (tmp_rows,tmp_checksum) exec ('select count(*), NULL from ' +@qualified_table_name + ' (TABLOCK HOLDLOCK)') where @qualified_table_name = sp_name. This causes the Distribution agent to perform the same transaction repeatedly without success.



WORKAROUND
To make the distribution agent function properly, delete the subscription and then resubscribe.

To prevent the problem from occurring again, do not use sp_publication_validation to validate the replication data while publications contain stored procedures as articles.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Additional query words: sql sp_publication_validation validate replication invalid object name

Keywords: kbbug kbpending KB275436

-

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

© Microsoft Corporation. All rights reserved.