Using LIKE breaks my openquery

  • Jay@Work

    SSCrazy

    Points: 2417

    I have an LDAP query that runs perfectly. However, as soon as I introduce a LIKE into the WHERE clause it breaks.

    The error (Msg 7321, Level 16, State 2, Line 1) seems to be generic rather than to do with my syntax so I'm not sure what is the issue. I have googled and seen others use LIKE within an openquery statement so am confused.

    This works fine

    OpenQuery (

    ADSI,

    'SELECT mail, mobile, telephoneNumber, title, sn, givenName, SAMAccountName FROM ''LDAP://xxx''

    WHERE objectClass = ''user'' AND givenName<=''z'' AND sn<=''z'' AND title>=''a'' AND mail<=''z'' AND ''userAccountControl:1.2.840.113556.1.4.803:''<>2') AS tblADS

    ORDER BY SAMAccountName

    This is broken

    OpenQuery (

    ADSI,

    'SELECT mail, mobile, telephoneNumber, title, sn, givenName, SAMAccountName FROM ''LDAP://xxx''

    WHERE objectClass = ''user'' AND givenName<=''z'' AND sn<=''z'' AND title>=''a'' AND mail<=''z'' AND ''userAccountControl:1.2.840.113556.1.4.803:''<>2 AND Mail like ''%xxx''') AS tblADS

    ORDER BY SAMAccountName

  • Sue_H

    SSC Guru

    Points: 89720

    Like won't work. You'd want to use star for the wildcard instead. Try something like Mail="*xxx"

    Sue

  • Jay@Work

    SSCrazy

    Points: 2417

    Perfect thanks! I googled openquery syntax to see what was and was not supported by gave up after a couple of pages of fluff.

    Is there a way to check for nulls? As you can see in my query I've had to use >= a or <= z to exclude null values or empty strings but every time I see the code I cringe

  • Sue_H

    SSC Guru

    Points: 89720

    I remember trying to find the right syntax for those...not fun. Try searching on LDAP filters. I think I found more information using that.

    Anyway, from what I remember you can try using just a star, such as Mail=*

    I think that it won't pull in null values but not sure on the empty strings. I haven't tested it.

    Sue

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply