Tagged: Replication

Cannot be configured for publishing and distribution. Publishing and distribution are supported only in SQL Server version 7.0 or later.

I got really wired issue when i was trying to configure Replication on my Enterprise edition 2016 server.

I checked couple of time and everything was looking okay until I realize I was running SSMS 2014.

1

I started SSMS 2016 and it’s fixed my issue.

I know its not very big issue but I thought to share with you all, just to save couple of min.

2

 

Advertisements

Replication – Snapshot is not getting generated

Replication issues and resolutions

Today I have encountered very strange issue –

Scenario – Snapshot is not getting generated for newly added articles

or

Error when setting up Transactional Replication via SQL scripts.

Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Step performed to resolved the issue-

  1. Re snap with reinitialize all subscription option no luck.
  2. Removed the newly added articles and add back and ran the snapshot but NO Luck.
  3. Drop the subscriber and add back and generate the snapshot but no luck ( Most of time it resolve the issue )
  4. Script out publication and drop the publication and when I try to add

While adding Publication i am getting error message –

Error Message –

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Resolution —

There are 2 unofficial workarounds:

(a) You can circumvent the check by specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article

(b) You could change the immediate_sync property in syspublications to 0 (see sp_changepublication).

Other more official workarounds —

Including changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only) and ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

Before —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’concurrent’,

@retention = 0,

@allow_push = N’true’,

 

After —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’native’,

@retention = 0,

@allow_push = N’true’,

 

  1. I have change the @sync_method = N’concurrent’ to @sync_method = N’native’ and ran the script to create publication.
  1. Ran the snapshot but snapshot not getting generated for newly added articles

7. Remove all article (from console) and added again … and ran the snapshot resolve my issue