Pages

Tuesday, December 30, 2025

Not Able to Add Article or Subscription After Upgrading

In SQL 2022, there is change in the linked server format used by replication for the listener subscriber with non default port, as shown below, the old format name is LISTENER,54321 and the new format is LISTENER2 and the port is in the provider string select name, provider_string from sys.servers
name              provider_string
LISTENER,54321    NULL
LISTENER          addr=tcp:LISTENER,54321
After the upgrade to SQL 2022 a couple of behaviors where observed: 

The first one is adding an article to a publication of a subscriber using the old format won’t generate a snapshot: A snapshot was not generated because no subscription needed initialization 

The second one is creating a new publication for an existing subscriber in the old format will get this error:

'SQL1' is not defined as a Subscriber for 'SQL2'. Could not update the distribution database subscription table. The subscription status could not be changed. The subscription could not be created. The subscription could not be found. Changed database context to 'dummy'. (Microsoft SQL Server, Error: 20032) 

Or 

Cannot insert the value NULL into column ‘freq_subday_interval’, table ‘distribution.dbo.MSrepl_agent_jobs’; column doen not allow nulls. UPDATE fails. Could not update the distribution database subscription table. The subscription status could not be changed. The subscription could not be created. The subscription could not be found. Changed database context to 'dummy'. (Microsoft SQL Server, Error: 20032) 

The workaround is to enable TF 15005 in both the publisher and distributor as well as creating aliases for the listener subscribers with non default port numbers, example LISTENER3 

If publisher and distributor are AG then aliases for the listener publisher and listener distributor are also needed, LISTENER1 and LISTENER2, respectively, in both 32 and 64-bit SQL Native Client, as shown below, in a PowerShell script
$aliases = @(
   @{name = 'LISTENER1'; value = 'DBMSSOCN,LISTENER1,54321'}
   @{name = 'LISTENER2'; value = 'DBMSSOCN,LISTENER2,54321'}
   @{name = 'LISTENER3'; value = 'DBMSSOCN,LISTENER3,54321'}
)

$registryPaths = @(
   'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
   'HKLM:\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo'
)
foreach ($alias in $aliases) {
   foreach ($registryPath in $registryPaths) {
      if (-not (Test-Path $registryPath)) {
          New-Item -Path $registryPath -Force
      }
      New-ItemProperty -Path $registryPath -Name $alias.name `
      -Value $alias.value -PropertyType String -Force
   }
}
Then you should be able to add the article or to create the subscription without the port number