为何OpenQuery在SQL函数外正常，函数内触发ADSDSOObject获取行错误？

为啥函数里会报错 #

SQL Server的标量值函数有严格的执行规则，你调用的ADSI链接服务器依赖ADSDSOObject这个OLE DB提供程序，刚好触发了两个限制：

• 标量函数要求操作必须是确定性的——输入相同参数必须返回完全一致的结果，但访问AD目录的操作做不到（AD数据可能随时更新、网络状态也可能变化），不符合函数的执行要求。
• 标量函数的运行环境限制了对外部资源的访问，ADSDSOObject这类调用外部系统的操作在该环境下无法正常获取结果集，因此抛出“无法获取行”的错误。

而直接执行的SELECT语句是在常规查询上下文，没有这些严格限制，所以能正常运行。

解决办法 #

换成表值函数 #

表值函数的限制比标量函数少，支持调用链接服务器的操作，代码修改如下：

CREATE FUNCTION [dbo].[cus_GetADLogin_TVF]
(   @displayName varchar(64) )
RETURNS TABLE
AS
RETURN
(
    SELECT sAMAccountName FROM 
        OpenQuery (ADSI, 'SELECT sAMAccountName, displayname FROM ''LDAP://mydomain.com'' WHERE objectCategory=''user''') AD
    WHERE AD.displayName = @displayName
)

调用方式：

SELECT sAMAccountName FROM dbo.cus_GetADLogin_TVF('Doug Kimzey')

改用存储过程 #

存储过程的运行环境完全支持访问外部资源，更适配这类场景，代码示例：

CREATE PROCEDURE [dbo].[cus_GetADLogin_SP]
    @displayName varchar(64),
    @sAMAccountName varchar(128) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @sAMAccountName = sAMAccountName FROM 
        OpenQuery (ADSI, 'SELECT sAMAccountName, displayname FROM ''LDAP://mydomain.com'' WHERE objectCategory=''user''') AD
    WHERE AD.displayName = @displayName
END

调用方式：

DECLARE @result varchar(128)
EXEC dbo.cus_GetADLogin_SP 'Doug Kimzey', @result OUTPUT
SELECT @result AS sAMAccountName

内容的提问来源于stack exchange，提问作者Doug Kimzey