SQL链接服务器-执行SP时发生远程访问错误 [英] SQL Linked server - Remote access error when executing SP

问题描述

我已经在SQL 2012和SQL 2008R2上的实例之间建立了链接服务器，但是 尝试在远程服务器上执行存储过程时，我不断收到以下错误:

I've set up a linked server between an instance on SQL 2012 and SQL 2008R2 but I keep getting the below error when trying to execute a stored procedure on a remote server:

 Msg 7201, Level 17, State 4, Line 1
Could not execute procedure on remote server 'TEST' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

我已经检查了两台服务器，并将它们配置为允许远程连接.我使用的登录名在远程服务器上具有sysadmin访问权限，在链接服务器中展开目录"时，我可以看到所有数据库.当我测试连接时，这也表示已成功连接. SQL Service帐户在两个实例上都具有SA，这是值得的

I've checked on both servers and they're configured to allow remote connections. The login that I've used has sysadmin access on the remote server and I can see all the databases when I expand 'Catalogs' within the linked server. When I test the connection this also says it's connected successfully. The SQL Service account also has SA on both instances for what it's worth

奇怪的是，当我在数据库中的一个表上运行一个简单的选择查询时，它显示了一个结果，但似乎并不喜欢SP.

The odd thing is when I run a simple select query on one of the tables within the database it shows a result but it doesn't seem to like the SP.

Select * from Linkserver.database.dbo.table 

上面的方法工作正常，但此SP无效:

The above works fine but this SP doesn't:

EXECUTE  Linkedserver.database.[dbo].[SP] ....

任何建议将不胜感激.

Any recommendation would be appreciated.

推荐答案

尽管是旧线程，但请忘记在远程服务器上启用远程访问(并重新启动服务)，然后尝试:

Despite being an old thread, forget about enabling remote access (and restart service) on remote server and try:

EXECUTE (N'database.[dbo].[SP]') AT Linkedserver

这篇关于SQL链接服务器-执行SP时发生远程访问错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！