Saturday, September 7, 2013

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

1 comment:

  1. more comments are in LinkedIn


Transitioning of SQLOS to SQLPAL

​ The SQLOS​ Prior to SQL Server 2005, SQL Server runs as a user mode process like any other Windows application and it depends on...