SSMS Limitations

When performing admin tasks or development tasks, some people like to use SQL Server Management Studio (SSMS) while some are like to use scripts. As many of you know, SSMS has limitations when it comes to perform some tasks. I found such instance while setting up replication for a testing purpose.

The requirement is simple, I wanted to setup a replication for a table. The uncommon factor here is, the publisher and subscriber databases are same. I used a AdventureWorks2008R2 as the database.

I first created the publication and then I used “New Subscriptions…” wizard in SSMS to add the subscription. See below screen;

The subscription database in this case is “AdventureWorks2008R2”. However I can not proceed further due to the information message shown bottom of dialog box.

“You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database”

This leaves me only the scripting option to perform the required task. So I’ve used the below script for to create the subscription.

use [AdventureWorks2008R2]
exec sp_addsubscription @publication = N'ADW2008R2_SalesOrderDetail_New', 
    @subscriber = N'SUSANTHABWIN7\SQL2012', @destination_db = N'AdventureWorks2008R2', 
    @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', 
    @update_mode = N'read only', @subscriber_type = 0