SQL Server Performance

LDAP query with a variable

Discussion in 'General Developer Questions' started by nryantx, Nov 14, 2006.

  1. nryantx New Member

    Hello,

    I can successfully query our Active Directory server to get various user information, but when I attempt to utilize a variable to do a lookup based on a single SID I don't get anything returned.

    ---------------------------------
    declare @varsid varchar(100);

    set @varsid = 'S-1...';

    select * from openquery
    (ADSI,
    'SELECT sAMAccountName, displayName, objectSID
    FROM ''LDAP://mydomain''
    WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND objectSID = '' & @varsid & ''
    '
    );
    ---------------------------------

    If I replace the variable in the WHERE clause with the actual value (S-1...) I'm setting, then the query returns me the correct SID and information.

    I've tried converting to varbinary and hex, but with the same results. I've tried using "+" signs instead of the "&" in the query, but no good. Am I using the variable incorrectly in the LDAP query?

    Thanks for any help
  2. Adriaan New Member

    Doesn't matter what kind of query you're building - this is about concatenating a string and a value from a variable.

    First of all, T-SQL only understands + for concatenation - so drop the &.

    Second, the value that you're concatenating into the query string needs to be delimited with single quotes. You already understand that you need to double up any single quotes when you are creating a query statement as a string - excellent.

    The next thing you need to understand is that the leading part of the statement needs three single quotes at the end: two to get a single quote in the string, and a third to separate the string from the 'outside' script (where your variable lives). To get the closing single quote after the concatenated value, you need to script 4 single quotes, not 3.

    If you have your script in Query Analyzer, you will notice that the variable name is also printed in red - meaning that indeed the name of the variable will be included in the query string, instead of the value from the variable.

    You need to add a single quote both before and after the concatenation operators, like so:

    declare @varsid varchar(100);
    set @varsid = 'S-1...';

    select * from openquery
    (ADSI,
    'SELECT sAMAccountName, displayName, objectSID FROM ''LDAP://mydomain''
    WHERE objectCategory = ''Person'' AND objectClass = ''user'' AND objectSID = ''' + @varsid + '''')

    If you copy this into QA, you will see that the variable name is again printed in black, meaning that its contents will be concatenated into the query string.
  3. nryantx New Member

    Adriaan, Thanks for the additional insight on the syntax

    I've played with the single quotes quite a bit and even as you suggest, but when I have the variable showing in black type in QA instead of red I get:

    Line 7: Incorrect syntax near '+'.

    I apologize for not being a SQL expert, but is there a package I may not have installed as part of my SQL Server 2000 install? I would expect the syntax to be interpreted the same, but just want to check.

    Thanks again
  4. Adriaan New Member

    Oomph - well, I started correcting the error, only to find that you can only execute OPENQUERY with a fixed query statement.

    So you need to use sp_ExecuteSQL, and double-up the already double-upped single quotes ...

    declare @varsid varchar(100), @SQL NVARCHAR(1000)
    set @varsid = 'S-1...'

    set @sql =
    N'select * from openquery(ADSI, ''SELECT sAMAccountName, displayName, objectSID FROM ''''LDAP://mydomain''''
    WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND objectSID = ''''' + @varsid + ''''''')'

    exec dbo.sp_executeSQL @SQL

    On my setup, I get just the "Could not find server 'ADSI' in sysservers. Execute sp_addlinkedserver to add the server to sysservers." message, meaning the query statement in itself is correct.

    You can also use EXEC ( ... ) which allows you to drop the @SQL variable and concatenate the query statement between the parentheses.
  5. Adriaan New Member

    ... and I should of course use proper sp_ExecuteSQL syntax with a parameter ...

    declare @varsid varchar(100)
    set @varsid = 'S-1...'

    exec dbo.sp_executeSQL
    N'select * from openquery(ADSI, ''SELECT sAMAccountName, displayName, objectSID FROM ''''LDAP://mydomain''''
    WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND objectSID = @varsid_internal)'')',
    N'@varsid_internal VARCHAR(100)',
    @varsid
  6. nryantx New Member

    Thank you very much Adriaan. I was able to get the first set of code to work, but had trouble with the second set. I'll continue to work with that, but thanks for the fix
  7. Adriaan New Member

    Using the proper parameter for sp_ExecuteSQL also cleans up the code you have to write - you can drop a lot of quotes there ...
  8. PedroPN New Member

    Dear friens,
    I'm having a problem with ADSI and SQL... Could you tell me, where is the error? The problem surge when I try to use parameter…

    "
    CREATE PROCEDURE TEST
    AS
    DECLARE @charVariable nvarchar(11);
    DECLARE @SQLString NVARCHAR(500);
    DECLARE @ParmDefinition NVARCHAR(500);

    /* Build the SQL string one time. */
    SET @SQLString = N'SELECT * FROM OPENQUERY(ADSI,''SELECT * FROM ''''LDAP://DC=GrupoCGD,DC=com'''' WHERE objectClass = ''''user'''' AND sAMAccountName=@UserID'')'

    /* Specify the parameter format one time. */
    SET @ParmDefinition = N'@UserID varchar(11)';

    /* Execute the string with the first parameter value. */
    SET @charVariable = 'rhs0002';
    EXECUTE sp_executesql @SQLString, @ParmDefinition,
    @userID = @charVariable;
    "

    ERROR is:
    Msg 7321, Level 16, State 2, Line 1
    An error occurred while preparing the query "SELECT * FROM 'LDAP://DC=GrupoCGD,DC=com' WHERE objectClass = 'user' AND sAMAccountName=@UserID" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".


    FOR EXAMPLE THIS VIEW WORKS…
    USE [dbGestaoDesktop]
    GO
    /****** Object: View [dbo].[AD_VW_DIR_Users] Script Date: 11/22/2006 11:53:13 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[AD_VW_DIR_Users]
    AS
    SELECT TOP (100) PERCENT cn, createTimeStamp,department, extensionAttribute1
    FROM OPENQUERY(ADSI, '
    SELECT createTimeStamp,cn, department, extensionAttribute1
    FROM ''LDAP://OU=DSO,OU=Utilizadores,OU=Servicos-Centrais,OU=cgd,DC=GrupoCGD,DC=com''

    WHERE objectclass=''user''


    ')
    AS MyTable
    WHERE (NOT (cn IS NULL))
    ORDER BY createTimeStamp

    THANKS
  9. Adriaan New Member

    Look at the details of the LDAP:// statement - they're totally different.
  10. PedroPN New Member

    Friend,
    the view is only an example to show you that I have the configurations ok in SQL Server. The source in SP is diferent from the VW, soo, LDAP is diferent. My question and problem is HOW to use parameter using LDAP in SQL SERVER!! THIS IS MY DOUBT!!

    THANKS!!!

Share This Page