Microsoft KB Archive/928320

Knowledge Base

You cannot use existing tables on a heterogeneous subscriber database when you configure a push subscription to a heterogeneous subscriber in SQL Server 2005

Article ID: 928320

Article Last Modified on 11/20/2007

---

APPLIES TO

• Microsoft SQL Server 2005 Standard Edition
• Microsoft SQL Server 2005 Developer Edition
• Microsoft SQL Server 2005 Enterprise Edition
• Microsoft SQL Server 2005 Standard X64 Edition
• Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
• Microsoft SQL Server 2005 Enterprise X64 Edition
• Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

---

SYMPTOMS

Consider the following scenario. You create a publication and configure a push subscription to a heterogeneous subscriber in Microsoft SQL Server 2005. For example, you configure a push subscription to an IBM DB2 heterogeneous subscriber. In the publication, you set the Action if name is in use article property to Keep existing object unchanged in SQL Server Management Studio. You synchronize the subscriber. In this scenario, you cannot use the existing tables on the heterogeneous subscriber database. The tables are always dropped and re-created.

RESOLUTION

To resolve this behavior, follow these steps:

1. Use the New Publication Wizard to create a transactional publication without creating a snapshot.
   
   To do this, follow these steps:
   1. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.
   2. In Object Explorer, expand Replication, right-click Local Publications, and then click New Publication to start the New Publication Wizard.
   3. Follow the New Publication Wizard to the Publication Type page.
   4. On the Publication Type page, select Transactional publication under Publication type, and then click Next.
   5. Follow the New Publication Wizard to the Snapshot Agent page.
   6. On the Snapshot Agent page, do not select Create a snapshot immediately and keep the snapshot available to initialize subscriptions. Click Next toward the end of the New Publication Wizard.
2. Enable the transactional publication for non-SQL Server subscribers.
   
   To do this, follow these steps:
   1. In Object Explorer, expand Local Publications, right-click the transactional publication that you just created, and then click Properties.
   2. In the Publication Properties dialog box, click Subscription Options in the Select a page pane.
   3. In the right pane, expand Creation and Synchronization, set the value of the Allow non-SQL Server Subscribers option to True.
   4. Click OK to close the Publication Properties dialog box.
3. Use the New Subscription Wizard to create a heterogeneous subscriber.
4. In SQL Server Management Studio, create a new query window, and then run the following command:

   sp_changearticle @publication= 'MyPublication', @article= 'MyArticle', @property='pre_creation_cmd', @value='none'

   Notes

   • MyPublication is the name of the transactional publication that you just created. MyArticle is the name of the article for which the property must be changed.
   • You must run this command for each article that is in the transactional publication.
5. Create a snapshot for the transactional publication.
   
   For more information about how to create a snapshot, visit the following Microsoft Developer Network (MSDN) Web site:

   http://msdn2.microsoft.com/en-us/library/ms151788.aspx

6. Synchronize the heterogeneous subscriber.

STATUS

This behavior is by design.

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005repl KB928320

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.