Microsoft KB Archive/260355

= BUG: Snapshot Job Adds Create Index Command for Transactional Publications Even if not Requested =

Article ID: 260355

Article Last Modified on 9/4/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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 &quot;Symptoms&quot; 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.

Additional query words: index creation script idx snapshot agent transactional replication article table

Keywords: kbbug kbpending KB260355

-

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

© Microsoft Corporation. All rights reserved.