April 9, 2019 at 9:30 pm
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
April 9, 2019 at 9:46 pm
Like won't work. You'd want to use star for the wildcard instead. Try something like Mail="*xxx"
Sue
April 9, 2019 at 9:54 pm
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
April 9, 2019 at 10:21 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply