‘%s’ is not configured as a distribution database.

Details
Product: SQL Server
Event ID: 14117
Source: MSSQLServer
Version: 10.0
Component: SQL Server Database Engine
Message: ‘%s’ is not configured as a distribution database.
   
Explanation

This error can occur if one or both of the following are true:

  • The entry for the specified distribution database is missing from
    msdb..MSdistributiondbs
    .

  • There is not an entry for the local server in the
    master
    database, or the entry that is there is incorrect.

    Replication expects all servers in a topology to be registered using the computer name with an optional instance name (in the case of a clustered instance, the SQL Server virtual server name with the optional instance name). For replication to function properly, the value returned by SELECT @@SERVERNAME for each server in the topology should match the computer name or virtual server name with the optional instance name.

    Replication is not supported if you have registered any of the SQL Server instances by IP address or by Fully Qualified Domain Name (FQDN). If you had any of the SQL Server instances registered by IP address or by FQDN in SQL Server Management Studio when you configured replication, this error could be raised.

   
User Action

Verify that the Distributor instance is registered properly. If the network name of the computer and the name of the SQL Server instance differ, either:

  • Add the SQL Server instance name as a valid network name. One method to set an alternative network name is to add it to the local hosts file. The local hosts file is located by default at WINDOWS\system32\drivers\etc or WINNT\system32\drivers\etc. For more information, see the Windows documentation.

    For example, if the computer name is comp1 and the computer has an IP address of 10.193.17.129, and the instance name is inst1/instname, add the following entry to the hosts file:

    10.193.17.129 inst1

  • Disable distribution, register the instance, and then reestablish distribution. For information about disabling distribution, see Disabling Publishing and Distribution. If the value of @@SERVERNAME is not correct for a non-clustered instance, follow these steps:

    sp_dropserver ”, ‘droplogins’
    go
    sp_addserver ”, ‘local’
    go

    After you execute the sp_addserver (Transact-SQL) stored procedure, you must restart the SQL Server service for the change to @@SERVERNAME to take effect.

    If the value of @@SERVERNAME is not correct for a clustered instance, you must change the name using Cluster Administrator. For more information, see Failover Clustering.

After verifying that the Distributor instance is registered properly, verify that the distribution database is listed in
msdb..MSdistributiondbs
. If it is not listed:

  1. Script out the distribution configuration. For more information, see Scripting Replication.

  2. Disable distribution and then re-enable it. For more information, see Configuring Distribution.

   
   
Version: 9.0
Component: SQL Server Database Engine
Message: ‘%s’ is not configured as a distribution database.
   
Explanation

This error can occur if one or both of the following are true:

  • The entry for the specified distribution database is missing from
    msdb..MSdistributiondbs
    .

  • There is not an entry for the local server in the
    master
    database, or the entry that is there is incorrect.

    Replication expects all servers in a topology to be registered using the computer name with an optional instance name (in the case of a clustered instance, the SQL Server virtual server name with the optional instance name). For replication to function properly, the value returned by SELECT @@SERVERNAME for each server in the topology should match the computer name or virtual server name with the optional instance name.

    Replication is not supported if you have registered any of the SQL Server instances by IP address or by Fully Qualified Domain Name (FQDN). If you had any of the SQL Server instances registered by IP address or by FQDN in SQL Server Management Studio when you configured replication, this error could be raised.

   
User Action

Verify that the Distributor instance is registered properly. If the network name of the computer and the name of the SQL Server instance differ, either:

  • Add the SQL Server instance name as a valid network name. One method to set an alternative network name is to add it to the local hosts file. The local hosts file is located by default at WINDOWS\system32\drivers\etc or WINNT\system32\drivers\etc. For more information, see the Windows documentation.

    For example, if the computer name is comp1 and the computer has an IP address of 10.193.17.129, and the instance name is inst1/instname, add the following entry to the hosts file:

    10.193.17.129 inst1

  • Disable distribution, register the instance, and then reestablish distribution. For information about disabling distribution, see Disabling Publishing and Distribution. If the value of @@SERVERNAME is not correct for a non-clustered instance, follow these steps:

    sp_dropserver ”, ‘droplogins’
    go
    sp_addserver ”, ‘local’
    go

    After you execute the sp_addserver (Transact-SQL) stored procedure, you must restart the SQL Server service for the change to @@SERVERNAME to take effect.

    If the value of @@SERVERNAME is not correct for a clustered instance, you must change the name using Cluster Administrator. For more information, see Failover Clustering.

After verifying that the Distributor instance is registered properly, verify that the distribution database is listed in
msdb..MSdistributiondbs
. If it is not listed:

  1. Script out the distribution configuration. For more information, see Scripting Replication.

  2. Disable distribution and then re-enable it. For more information, see Configuring Distribution.

   
   
Version: 8.0
Component: SQL Engine
Message: ‘%s’ is not configured as a distribution database.
   
Explanation
This error can occur if any one or more of the following are true:

  1. There is no entry for the local server in the sysservers table in the master database, or the entry is incorrect. To verify the local server setting, connect to the local server and run “select @@servername” from Query Analyzer. If a (NULL) is returned, an entry for the local server does not exist in the sysservers table in the master database. If a value other than the Computer Name or SQL Virtual Name is returned, the entry is incorrect, perhaps because the computer was renamed. Note that a restart of SQL Server is required for changes to the local server to be reflected in @@servername.
  2. The entry for the specified distribution database is missing from msdb..MSdistributiondbs.
  3. The replication bitmap stored in the category column of sysdatabases does not reflect that the specified database is used for distribution.
   
User Action
Follow these steps:

  1. Execute “SELECT @@servername” and “exec master..xp_cmdshell ‘set computername'”. When ‘set computername’ is executed from Query Analyzer or OSQL for a clustered instance, it returns the SQL Virtual Name rather than the node name, and the SQL Virtual Name is the value needed for replication.

    • For a nonclustered default instance, “SELECT @@servername” should return the same value as “exec master..xp_cmdshell ‘set computername'”.
    • For a nonclustered named instance, “SELECT @@servername” should return the value as “exec master..xp_cmdshell ‘set computername'” plus “\” plus the name of the instance.
    • For a clustered default instance, “SELECT @@servername” should return the same value as “exec master..xp_cmdshell ‘set computername'”.
    • For a clustered named instance, “SELECT @@servername” should return the value as “exec master..xp_cmdshell ‘set computername'” plus “\” plus the name of the instance.

    If the value of @@servername is not correct or is NULL for a nonclustered instance, follow these steps:sp_dropserver ‘{OLD_NAME}’, ‘droplogins’ go sp_addserver ‘{NEW_NAME}’, ‘local’ go

    Note: After you run the sp_addserver stored procedure, you must restart the SQL Server service for the change to @@SERVERNAME to take effect.

    If the value of @@server is not correct for a clustered instance, you must uninstall and reinstall the clustered instance to correct the name.

  2. Verify that your distribution database is listed in msdb..MSdistributiondbs. If it is not, script out your replication as a backup. Run sp_dropdistributor @no_checks=no_checks. Re-add replication.
  3. Verify in sysdatabases that your distribution database is marked for distribution. To get a list of all databases that are configured as distribution databases:
    select category, * from master..sysdatabases where category & 16 {greater than sign | less than sign } 0
    If your distribution database is not in the results, then it is not configured for replication. Script out your replication as a backup. Run sp_dropdistributor @no_checks=no_checks. Re-add replication./OL>

Related:

Leave a Reply