BUG: Distribution Agent Fails When Validating Publication with Stored Procedure Articles (275436)

SYMPTOMS

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

Invalid object name 'sp_name'

"sp_name" 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 "sp_name" 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