为函数构建查询字符串以通过 SQL Server 查询 LDAP

Question

INPUT_STRING NVARCHAR(MAX)
RETURNS NVARCHAR(MAX)
AS 
BEGIN
DECLARE @NewString NVARCHAR(MAX)
DECLARE @SelectClause NVARCHAR(MAX)
SET @INPUT_STRING = @NewString
SET @SelectClause = 'SELECT *  FROM OpenQuery (' + ' 
  ADSI, ' + '
  ''SELECT  samaccountname
  FROM ''LDAP://' +  @NewString  +
  ')'' + '' AS tblADSI'''
 EXEC sp_EXECUTESQL @SelectClause

输入字符串是这样的

CN=firstName, Lastname,OU=MyOU,OU=AnotherOU,OU=Accounts,DC=enterprisenet,DC=org

错误消息为读取。

Msg 105，级别 15，状态 1，第 8
行 字符串 '(' + ' AS tblADSI' 后面的未闭合引号 执行sp_EXECUTESQL @SelectClause

任何帮助将非常感谢。

Answer 1

试试这个....

declare @INPUT_STRING nvarchar(MAX); 
SET @INPUT_STRING = 'CN=firstName, Lastname,OU=MyOU,OU=AnotherOU,OU=Accounts,DC=enterprisenet,DC=org';
DECLARE @NewString NVARCHAR(MAX)
DECLARE @SelectClause NVARCHAR(MAX)
SET @NewString = @INPUT_STRING 
SET @SelectClause = N'SELECT *  FROM OpenQuery (ADSI,'
                 +  N'  ''SELECT  samaccountname '
                 +  N'  FROM ''''LDAP://' +  @NewString  + N''''' '') AS tblADSI' 

检查

PRINT @SelectClause

SELECT *  
FROM OpenQuery (ADSI,  
               'SELECT  samaccountname   
                FROM ''LDAP://CN=firstName, Lastname,OU=MyOU,OU=AnotherOU,OU=Accounts,DC=enterprisenet,DC=org'' 
               ') AS tblADSI