无法对数据库设置复制

Question

执行：

USE master
GO
exec sp_replicationdboption 
@dbname = N'test', 
@optname = N'publish', 
@value = N'true'

报错：

链接服务器"repl_distributor"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "Login timeout expired"。
链接服务器"repl_distributor"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."。
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Answer 1

根据错误提示，是访问分发服务器超时，检查你的分发服务器配置是否正确，以及是否可以从你的发布服务器访问（使用名称）

Answer 2

谢谢邹建大哥，我的对test数据库做事务发布的时候，

报错：

不允许此数据库用于发布
无法执行过程，该数据库没有发布。Error:14013

于是我就执行了

USE master
GO
exec sp_replicationdboption 
@dbname = N'test', 
@optname = N'publish', 
@value = N'true'

然后就报链接服务器"repl_distributor"的错误。

我查看了EXEC sp_helpserver，信息如下：

name      network_name       status

repl_distributor    repl_distributor        rpc,dist,rpc out,use remote collation

我又执行了：SELECT @@SERVERNAME ,serverproperty('servername')

发现@@SERVERNAME是xdb05

serverproperty('servername')是XDB05

是不是因为大小写不一样导致的？

Answer 3

Did you config distributor properly? Tried set replication with wizard in ssms?

Answer 4

大小写不敏感的，跟大小写无关

---

给我写信： QQ我：

Answer 5

以下是微软的建议文档，着重关注下你的发布数据库和分发数据库是否已经开启复制功能：

Details
Product:	SQL Server
Event ID:	14013
Source:	MSSQLServer
Version:	8.0
Component:	SQL Engine
Message:	This database is not enabled for publication.
Explanation
You are trying to add, activate, or modify an article or publication to a database that is not configured for the type of publication specified (transactional or merge). A database can be configured for either transactional publication only, merge publication only, or both transactional and merge publication in the Publisher and Distributor properties. More information on how to modify the Publisher and Distributor properties can be found in "Replication Properties" in Books Online. This error can typically occur if you have never enabled the database for the specified type of publication or if a published database has been restored to the same or a different instance without the KEEP_REPLICATION option.
User Action
Enable the database for the appropriate types of replication (transactional or merge). More information on how to modify the Publisher and Distributor properties can be found in "Replication Properties" in Books Online. If you are using scripts to set up your replication, verify that the correct database parameters are specified. If you have restored a replicated database and no essential changes have been made to the database since the restore, you can execute the restore again, but this time use Transact-SQL and specify the KEEP_REPLICATION option. For more information on the KEEP_REPLICATION option, see "RESTORE" in Books Online.

---

Answer 6

通过SSMS 办面去检查一下，只看那个，是看不到所有分发所需的完整信息的

Answer 7

关键问题是，现在就是我开启数据库功能时报错了：

exec sp_replicationdboption 
@dbname = N'test', 
@optname = N'publish', 
@value = N'true'

报错：

链接服务器“repl_distributor”的OLE DB访问接口“SQLNCLI”返回了消息“登录超时已过期”。
链接服务器“repl_distributor”的OLE DB访问接口“SQLNCLI”返回了消息“发生了错 ​​误，而建立一个连接到服务器连接到SQL Server 2005时，此故障可能造成的事实，在默认的设置下SQL Server不会允许远程连接。“ 
消息53，级别16，状态1，行1 
命名管道提供程序：无法打开连接到SQL Server [53]。

Answer 8

想问一下你发布或者订阅的数据库是链接服务器里的数据库吗？

---

给我写信： QQ我：

Answer 9

关键问题是，现在就是我开启数据库功能时报错了：

exec sp_replicationdboption 
@dbname = N'test', 
@optname = N'publish', 
@value = N'true'

报错：

链接服务器“repl_distributor”的OLE DB访问接口“SQLNCLI”返回了消息“登录超时已过期”。
链接服务器“repl_distributor”的OLE DB访问接口“SQLNCLI”返回了消息“发生了错 ​​误，而建立一个连接到服务器连接到SQL Server 2005时，此故障可能造成的事实，在默认的设置下SQL Server不会允许远程连接。“ 
消息53，级别16，状态1，行1 
命名管道提供程序：无法打开连接到SQL Server [53]。

What's your sql edition? Did you config distributor first? You should answer questions for people to help you, keep posting errors doesn't do any good. 

Answer 10

我建了一个库test，然后对方进行发布，我首先执行：

exec sp_replicationdboption 
@dbname = N'test', 
@optname = N'publish', 
@value = N'true'

就报错，我怀疑是不是sqlserver安装有问题。

Answer 11

sqlserver2005 RTM,在ssms操作报错：

SQL Server counld not create publication 'test'.

Addittional information:

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

(Micrsoft.SqlServer.ConnectionInfo)

不允许此数据库用于发布。

无法执行过程。该数据库没有发布。请在已为复制发布的数据库中执行该过程。

已将数据库上下文更改为‘test’.(Microsoft SQL Server,Error:14013)

Answer 12

想问一下你的sqlserver是什么版本，企业版？express版？标准版？个人开发者版？

---

给我写信： QQ我：

Answer 13

是企业版

Answer 14

can you run following stored procedure and post the result? This will show whether you have configured Distribution .

sp_get_distributor

Answer 15

运行sp_get_distributor显示如下：

[installed]:1

[distribution server]:repl_distributor 

[distribution db installed]:0

[is distribution publisher]:0

 [has remote distribution publisher]:0

Answer 16

Looks like didn't set distributor properly.

Answer 17

奇怪的问题是：

1、distribution数据库不是在System Databases下。

2、这个sqlserver实例的名字是xdb05,然后我再一次执行：

exec sp_adddistributor @distributor = N'xdb05', @password = N''

报错：已将服务器 'repl_distributor' 定义为分发服务器。

Answer 18

Better to reconfigure replication from scratch. Remove all publishers and distributor, recreate distributor then add publish. Ensure no error in each step.