SQL Server Replication Error: cannot create publication because the server is too old

By:   |   Comments (3)   |   Related: > Replication


Problem

I was creating a new publication on SQL Server using SQL Server Management Studio and I encountered this error “You cannot create a publication from server XXX because the server is too old”.

Solution

I will walk you through the setup that caused this problem and what I found.

Create New Publication

I installed SQL Server 2016 on a machine and I wanted to setup transactional replication. I was using SQL Server Management Studio 2014 to connect to this new instance to create the new publication. I right clicked on Local Publication under Replication and selected New Publication.

new publication

When I selected New Publication, it showed me this error "You cannot create a publication from server 'TTI412-VM\SQL2016' because the server is too old”.

object explorer

This was a brand-new installation and I was perplexed by the error message, so I did a few things to make sure things were setup correctly.

  1. I check to make sure the replication components were installed correctly on the SQL Server 2016 instance. I did not see any issues.
  2. To verify that SSMS was not the issue, I created a publication using SSMS 2014 on a SQL Server 2014 instance with the publication wizard and it worked properly.

Creating New Publication Script

As mentioned, I was able to create a publication on the SQL Server 2014 instance, so I generated a publication script. To do that, I right clicked on the publication and selected Generate Scripts as shown in the below image.

generate scripts

I made the required changes to the script, so I could execute it against the SQL Server 2016 instance using SSMS 2014 and the script executed successfully.

-- Enabling the replication database
USE master
 
EXEC Sp_replicationdboption
  @dbname = N'Images',
  @optname = N'publish',
  @value = N'true'
 
go
 
EXEC [Images].sys.Sp_addlogreader_agent
  @job_login = NULL,
  @job_password = NULL,
  @publisher_security_mode = 1
 
go
 
-- Adding the transactional publication
USE [Images]
 
EXEC Sp_addpublication
  @publication = N'Images',
  @description =
N'Transactional publication of database ''Images'' from Publisher ''TTI412-VM\SQL2016''.'
,
@sync_method = N'concurrent',
@retention = 0,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true',
@immediate_sync = N'true',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'false',
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
 
go
 
EXEC Sp_addpublication_snapshot
  @publication = N'Images',
  @frequency_type = 1,
  @frequency_interval = 0,
  @frequency_relative_interval = 0,
  @frequency_recurrence_factor = 0,
  @frequency_subday = 0,
  @frequency_subday_interval = 0,
  @active_start_time_of_day = 0,
  @active_end_time_of_day = 235959,
  @active_start_date = 0,
  @active_end_date = 0,
  @job_login = NULL,
  @job_password = NULL,
  @publisher_security_mode = 0,
  @publisher_login = N'sa',
  @publisher_password = N'Your SA password'
 
EXEC Sp_grant_publication_access
  @publication = N'Images',
  @login = N'sa'
 
go
 
EXEC Sp_grant_publication_access
  @publication = N'Images',
  @login = N'TTI412-VM\Administrator'
 
go
 
EXEC Sp_grant_publication_access
  @publication = N'Images',
  @login = N'NT SERVICE\SQLAgent$SQL2017'
 
go
 
EXEC Sp_grant_publication_access
  @publication = N'Images',
  @login = N'NT SERVICE\Winmgmt'
 
go
 
EXEC Sp_grant_publication_access
  @publication = N'Images',
  @login = N'NT SERVICE\SQLWriter'
 
go
 
EXEC Sp_grant_publication_access
  @publication = N'Images',
  @login = N'distributor_admin'
 
go
 
-- Adding the transactional articles
USE [Images]
 
EXEC Sp_addarticle
  @publication = N'Images',
  @article = N'FileList',
  @source_owner = N'dbo',
  @source_object = N'FileList',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x000000000803509F,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'FileList',
  @destination_owner = N'dbo',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_dboFileList]',
  @del_cmd = N'CALL [sp_MSdel_dboFileList]',
  @upd_cmd = N'SCALL [sp_MSupd_dboFileList]'
 
go
 
USE [Images]
 
EXEC Sp_addarticle
  @publication = N'Images',
  @article = N'Images',
  @source_owner = N'dbo',
  @source_object = N'Images',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x0000000000000087,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'Images',
  @destination_owner = N'dbo',
  @status = 16,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_dboImages]',
  @del_cmd = N'CALL [sp_MSdel_dboImages]',
  @upd_cmd = N'MCALL [sp_MSupd_dboImages]'
 
go
			

After creating the publication, I could subscribe to the publication from other servers without any error.

Conclusion

I could create the publication on SQL Server 2016 using scripts, but I did not understand why I was not able to create the publication using the UI.

As you may know, SQL Server 2016 does not provide an option to install SSMS that is why I was using SSMS 2014.  In order to get the latest version of SSMS you must download it separately. I downloaded and installed the latest version of SQL Server Management Studio and opened it and connected to the SQL Server 2016 instance. Once again, I tried to create a new publication. I right clicked on Local Publications under Replication and clicked on New Publication and the New Publication Wizard started successfully as shown below.

new publication wizard

In short, the error was generated because of an older SSMS version, not because of an old SQL Server version. The message was a little misleading, so I hope this tip helps others that may encounter this problem.

I would like to thank my friend Dolly Gupta who helped me with this article.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nisarg Upadhyay Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Saturday, April 4, 2020 - 11:41:58 AM - Syed Saqib Hassan Back To Top (85278)

I am trying to set up replication using SQL Server 2012. The publisher, distributor, and subscriber are all local.

In the section "Configure the publisher for transactional replication" after completing the last step action that was 'complete wizard' I click to Finish then I faced this error.

SQL Server could not create publication 'NorthwindPublication'.

Additional information:

-> An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

-> The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

     The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

     Changed database context to 'Northwind'. (Microsoft SQL Server, Error: 50000)

Anyone can help me out to this problem?


Tuesday, March 17, 2020 - 11:56:49 AM - Qaiser Mehmood Back To Top (85129)

Thanks, Nisarg for the tip. It is really a helpful one.


Thursday, July 18, 2019 - 2:04:42 PM - paul lee Back To Top (81792)

I installed new SSMS tool from web site. it seems to be working with new SSMS tool.  Version 17.7















get free sql tips
agree to terms