不幸的是，EXECUTE .. AT 在涉及参数时不太方便，因为它只支持 ? 语法:EXEC ('EXECUTE CRM.dbo.GetCustomer @Customer=?', 619) AT 螺丝刀参数名称在这里是可选的，但我强烈建议使用它来保持可预测性.与 EXECUTE 相同——它是可选的，但它清楚地表明我们确实在运行任意查询，而不仅仅是调用存储过程.如果你的程序有 OUTPUT 参数，这个简单的将不起作用；EXECUTE .. AT 不够聪明.您可以在调用中指定 OUTPUT，但该值不会被复制回来.解决方法超出了本答案的范围，但它们不会很好.为此打开一个建议可能是值得的，因为它看起来像那种真正应该按照文档工作的东西(如果微软想永久摆脱远程访问，他们无论如何都需要这样做).We currently execute a stored procedure against a linked server using:EXECUTE [LinkedServer].[DatabaseName].[dbo].[MyProcedure]For example:EXECUTE Screwdriver.CRM.dbo.GetCustomer 619And this works fine; querying through a linked server works fine.Disabling the deprecated "Remote Access" featureThere is apparently a little known, seldom used, feature known as remote access. Microsoft has very little to say about what this feature is, except to say here:And from the SQL Server 2000 Books Online:We only ever added linked servers, and have never used this "remote access" feature, and have never added a server using sp_addserver.We're all good. Right?Except turning off remote access breaks everythingAn auditor has mentioned that we should turn off the remote access feature:it's a security checkbox on their clipboardit's deprecated by Microsoftit's hardly ever usedand we don't use itShould be fine, right?Microsoft documents how to turn off this hardly used feature:Except when we do: everything goes to hell:Worse than failure? Just to be absolutely sure I'm using a linked server, I:EXECUTE sp_dropserver @server='screwdriver', @dropLogins='droplogins'EXECUTE sp_addlinkedserver N'screwdriver', N'SQL Server'and re-run my procedure call:EXECUTE Screwdriver.CRM.dbo.GetCustomer 619Worse than failure!?I can confirm that the server is a linked server (and is not a "remote" server) using:SELECT SrvName, IsRemoteFROM master..sysserversWHERE SrvName = 'Screwdriver'Srvname IsRemote----------- --------screwdriver 0Or using the modern objects:SELECT Name, Is_LinkedFROM sys.serversWHERE Name = 'Screwdriver'Name Is_linked----------- --------screwdriver 1To Sum UpWe're at the point now where:I've disabled remote accessRemote access only applies to servers added through sp_addserverit doesn't apply to servers added through sp_addlinkedserverI'm accessing a server added through sp_addlinkedserverWhy isn't it working?Which brings me to my question:How to execute a stored procedure against a linked server?And the corollary:How to not execute a stored procedure against an added (i.e. "remote") server?Bonus ChatterThe remote access configuration option, that you adjust using sp_configure, is also exposed through the user interface. The SSMS UI describes the feature incorrectly:It incorrect phrasing is:It should be phrased:The Books Online also document the feature incorrectly:It should be:It makes sense that the youngsters at Microsoft these days don't remember what a 20 year old deprecated feature that they've never touched does.Documentation from BOL 2000SQL Server 2000 was the last time this feature was documented. Reproduced here for posterity and debugging purposes:See alsoExecuting a Stored Procedure on a Linked ServerSQL Linked server - Remote access error when executing SPSQL Server Error: Could not execute remote procedureMSDN Blogs: Unable to execute a remote stored procedure over a linked server (archive.is)Configure the remote access Server Configuration Optionsp_addlinkedserver (Transact-SQL)sp_configure (Transact-SQL)Security Audit requires turning Remote Access off on all SQL 2005 and SQL 2008 ServersAlternate title: Disabling SQL Server Remote Access breaks stored procedures. 解决方案 Well, when you're right you're right, whether it's documented or not. Setting remote access to 0 (and restarting) causes remote stored procedure calls using four-part syntax to fail, even though all the documentation suggests it should not fail for linked servers. This is true even on the most recent build of SQL Server 2017 (RTM CU12), so this is not version specific. It's not clear if this is a real restriction, or if the code is just buggy and blocking it based on the remote access feature check even though it would technically work.Queries involving four-part names (SELECT * FROM server.db.scheme.table) will not fail, presumably because this only works for linked servers and never involved remote access in the first place.As a workaround, you can change the call to use EXECUTE .. AT:EXEC ('EXECUTE CRM.dbo.GetCustomer 619') AT ScrewdriverThis works as long as the linked server has the RPC Out option enabled (which it will be by default if added by sp_addlinkedserver with no special options).Unfortunately EXECUTE .. AT is much less convenient when parameters are involved, because it only supports the ? syntax:EXEC ('EXECUTE CRM.dbo.GetCustomer @Customer=?', 619) AT ScrewdriverThe parameter name is optional here, but I'd strongly recommend using it to keep things predictable. Same with EXECUTE -- it's optional, but it makes it clear that we're really running an arbitrary query, not just calling the stored procedure.If your procedure has OUTPUT parameters, this plain will not work; EXECUTE .. AT isn't clever enough for that. You can specify OUTPUT in the call, but the value will not be copied back. Workarounds for that are beyond the scope of this answer, but they won't be pretty.It may be worth opening a suggestion for this, because it seems like the sort of thing that really ought to work as documented (and if Microsoft ever wants to get permanently rid of remote access, they'll need to anyway). 这篇关于如何对链接服务器执行存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！
