BUG: Snapshot Job Adds Create Index Command for Transactional Publications Even if not Requested (260355)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q260355
BUG #: 57724 (SQLBUG_70)

SYMPTOMS

Transactional publications with articles created with @schema_option 0x00, which specifies not to create indexes or tables, fail with this error message in the distribution agent:
OS error 2:The system cannot find the file specified

CAUSE

The snapshot agent of the publication erroneously adds an entry into the distribution.dbo.msrepl_commands table referencing an index creation script file (.idx file), although the index creation script file is not created on the distributor. Therefore, the distribution agent fails with the error message shown in the "Symptoms" section.

WORKAROUND

Specify @schema_option=0x03 while creating the articles using the sp_addarticle stored procedure. For example: 
sp_addarticle @publication = N'snapshotidxpub', @article = N't1',
@source_owner = N'dbo', @source_object = N't1', @destination_table = N't1',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'none', @schema_option = 0x0000000000000003,
@status = 16, @vertical_partition = N'false', @ins_cmd = N'SQL',
@del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null,
@filter_clause = NULL
The preceding workaround of using @schema_option 0x03 creates a .sch file, which creates tables on the subscriber if they do not exist. However, this workaround cannot be applied to heterogeneous subscribers.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.
Modification Type:MajorLast Reviewed:9/4/2002
Keywords:kbbug KB260355
©2002 Microsoft Corporation. All rights reserved.