Solved

Using LEFT function in dynamic SQL call to ADSI

Posted on 2010-11-29
15
756 Views
Last Modified: 2012-05-10
I have a linked sever to AD which works fine except I'm trying to query AD regarding all user whose e-mail address ends in 'manager'.  When I do this I get the error, Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT      EmployeeNumber,
                                     GivenName,
                                     SN,  
                                    sAMAccountName,
                                    TelephoneNumber,
                                    FacsimileTelephoneNumber,
                                    mail
                     FROM             'LDAP://DC=People-Inc,DC=net'
                     WHERE              objectCategory = 'Person'
                  AND             objectClass = 'user'
                  AND                  LEFT(mail,7) = 'manager'" for execution against OLE DB provider "ADSDSOOBJECT" for linked server "ADSI".

You can see the code in the error.  If i remove the line that says, AND      LEFT(mail,7) = 'manager' I Don't get the error.  My assumption is there is a problem with using things like the LEFT function.  I also tried it using the LIKE function with the same results.  Any assistance would be greatly appreciated.
0
Comment
Question by:leskelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34231875
Try this:

SELECT G.EmployeeNumber,
                                     G.GivenName,
                                     G.SN,  
                                    G.sAMAccountName,
                                    G.TelephoneNumber,
                                    G.FacsimileTelephoneNumber,
                                    G.mail
FROM 'LDAP://DC=People-Inc,DC=net' G

INNER JOIN (SELECT 'manager' AS Val
 )G1
ON G.mail LIKE '%' + Gt1.Val

0
 

Author Comment

by:leskelly
ID: 34231959
Hello and thanks for the response but I get the same thing,
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT      G.EmployeeNumber,
                                     G.GivenName,
                                     G.SN,  
                                    G.sAMAccountName,
                                    G.TelephoneNumber,
                                    G.FacsimileTelephoneNumber,
                                    G.mail
                     FROM             'LDAP://DC=People-Inc,DC=net' G
                     INNER JOIN      (SELECT 'manager' AS Val) G1
                     ON                  G.mail LIKE '%' + G1.Val" for execution against OLE DB provider "ADSDSOOBJECT" for linked server "ADSI".

I've attached the actual code so you can see the placement of all the qoutes.
SELECT EmployeeNumber AS [Employee Number], GivenName AS 'First Name', SN AS 'Last Name', sAMAccountName AS 'User Name', TelephoneNumber AS 'Phone', FacsimileTelephoneNumber AS 'Fax Number', mail AS Email
		FROM	
		OPENQUERY(ADSI, 
			 'SELECT	G.EmployeeNumber,
	 					G.GivenName, 
	 					G.SN,  
						G.sAMAccountName, 
						G.TelephoneNumber, 
						G.FacsimileTelephoneNumber,
						G.mail
   			FROM 		''LDAP://DC=People-Inc,DC=net'' G
   			INNER JOIN	(SELECT ''manager'' AS Val) G1
   			ON			G.mail LIKE ''%'' + G1.Val')

Open in new window

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34232237
Please check to see if your linked server (ADSI) allow remote accessing.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 

Author Comment

by:leskelly
ID: 34232257
Yes it does as I said above if I remove the last condition of my original code then the query works and returns data.
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34232943
Try my code again. I think it is missin )
Or Try your original query again. Only this time, try this



SELECT EmployeeNumber AS [Employee Number], GivenName AS 'First Name', SN AS 'Last Name', sAMAccountName AS 'User Name', TelephoneNumber AS 'Phone', FacsimileTelephoneNumber AS 'Fax Number', mail AS Email
		FROM	
		OPENQUERY(ADSI, 
			 'SELECT EmployeeNumber,
	 			 GivenName, 
	 			 SN,  
				 sAMAccountName, 
				 TelephoneNumber, 
				 FacsimileTelephoneNumber,
				 mail
                    FROM 'LDAP://DC=People-Inc,DC=net'
                    WHERE  objectCategory = 'Person' 
                    AND    objectClass = 'user'
                    AND    mail LIKE ''%'' + ''managerSELECT EmployeeNumber AS [Employee Number], GivenName AS 'First Name', SN AS 'Last Name', sAMAccountName AS 'User Name', TelephoneNumber AS 'Phone', FacsimileTelephoneNumber AS 'Fax Number', mail AS Email
		FROM	
		OPENQUERY(ADSI, 
			 'SELECT EmployeeNumber,
	 			 GivenName, 
	 			 SN,  
				 sAMAccountName, 
				 TelephoneNumber, 
				 FacsimileTelephoneNumber,
				 mail
                    FROM 'LDAP://DC=People-Inc,DC=net'
                    WHERE  objectCategory = 'Person' 
                    AND    objectClass = 'user'
                    AND    mail LIKE ''%'' + ''manager''')

Open in new window

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34232959
I didn't realize I copied it twice sorry.

Again, try the original code I posted and enclose it with )

Then if for some reason it still fails, then try this:

SELECT EmployeeNumber AS [Employee Number], GivenName AS 'First Name', SN AS 'Last Name', sAMAccountName AS 'User Name', TelephoneNumber AS 'Phone', FacsimileTelephoneNumber AS 'Fax Number', mail AS Email
		FROM	
		OPENQUERY(ADSI, 
			 'SELECT EmployeeNumber,
	 			 GivenName, 
	 			 SN,  
				 sAMAccountName, 
				 TelephoneNumber, 
				 FacsimileTelephoneNumber,
				 mail
                    FROM 'LDAP://DC=People-Inc,DC=net'
                    WHERE  objectCategory = 'Person' 
                    AND    objectClass = 'user'
                    AND    mail LIKE ''%'' + ''manager''')

Open in new window

0
 

Author Comment

by:leskelly
ID: 34233082
I tried the last code you posted and got the same results.  Where are you thinking I need to put parenthesis in the code you originally posted?
0
 

Author Comment

by:leskelly
ID: 34233206
I tried another query using the LIKE operator against a different linked server that links to another SQL Server and it works fine.  I've attached the code.  I wonder if's just not possible to do anything but a basic select against AD.
SELECT *
		FROM	
		OPENQUERY(Intranet, 
			 'SELECT *
                    FROM [Client Track].dbo.tabCoreInformation
                    WHERE City LIKE ''Buff%''')

Open in new window

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34233217
See the
bolded

Open in new window


SELECT EmployeeNumber AS [Employee Number], GivenName AS 'First Name', SN AS 'Last Name', sAMAccountName AS 'User Name', TelephoneNumber AS 'Phone', FacsimileTelephoneNumber AS 'Fax Number', mail AS Email
            FROM      
            OPENQUERY(ADSI,
                   'SELECT      G.EmployeeNumber,
                                     G.GivenName,
                                     G.SN,  
                                    G.sAMAccountName,
                                    G.TelephoneNumber,
                                    G.FacsimileTelephoneNumber,
                                    G.mail
                     FROM             ''LDAP://DC=People-Inc,DC=net'' G
                     INNER JOIN      (SELECT ''manager'' AS Val) G1
                     ON                  G.mail LIKE ''%'' + G1.Val'))

Open in new window

0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 34233224
actually, just try the one I just posted
0
 

Author Comment

by:leskelly
ID: 34233252
There is a ) without a matching ( in the last line.  If I remove it I get the same message.
0
 

Accepted Solution

by:
leskelly earned 0 total points
ID: 34234508
The condition that checks for the manager has to go outside of the OpenQuery statement as in the code below.
SELECT AD.EmployeeNumber AS [Employee Number], GivenName AS 'First Name', SN AS 'Last Name', sAMAccountName AS 'User Name', TelephoneNumber AS 'Phone', FacsimileTelephoneNumber AS 'Fax Number', mail AS Email
		FROM	
		OPENQUERY(ADSI, 
			 'SELECT	EmployeeNumber,
	 					GivenName, 
	 					SN,  
						sAMAccountName, 
						TelephoneNumber, 
						FacsimileTelephoneNumber,
						mail
   			FROM 		''LDAP://DC=People-Inc,DC=net''
   			WHERE  		SN = ''_''') AD
		WHERE sAMAccountName LIKE '%manager%'

Open in new window

0
 

Author Comment

by:leskelly
ID: 34408979
I did some more research and another posting gave me the answer.  The code inside the OPENQUERY must use the syntax of the server being queried in this case Active Directory.  So 'like' and 'left' don't work.  Also the wild-card character is * not %.  I've attached some code that will work.
DECLARE @Manager CHAR(8)
SET @Manager = '-manager'
DECLARE @ManagerQuery varchar(2000)
SET @ManagerQuery = 'SELECT EmployeeNumber AS [Employee Number], GivenName AS ''First Name'', SN AS ''Last Name'', sAMAccountName AS ''User Name'', TelephoneNumber AS ''Phone'', FacsimileTelephoneNumber AS ''Fax Number'', mail AS Email
FROM	
OPENQUERY(ADSI, ''SELECT	EmployeeNumber,
				GivenName, 
				SN,  
				sAMAccountName, 
				TelephoneNumber, 
				FacsimileTelephoneNumber,
				mail
	FROM 		''''LDAP://DC=People-Inc,DC=net''''
	WHERE  		objectCategory = ''''Person'''' 
	AND 		objectClass = ''''user'''' 
	AND 		sAMAccountName = ''''*' + @Manager + ''''''')'
EXEC(@ManagerQuery)

Open in new window

0
 

Author Comment

by:leskelly
ID: 34411387
Another tidbit I picked up on this subject is how to query AD for records in which a specific field is not null.  Thing s like IS NOT NULL and !> null don't work.  What does work is something like
WHERE EmployeeNumber = ''*''
0
 

Author Closing Comment

by:leskelly
ID: 34424421
I found my own solution.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

While rebooting windows server 2003 server , it's showing "active directory rebuilding indices please wait" at startup. It took a little while for this process to complete and once we logged on not all the services were started so another reboot is …
Always backup Domain, SYSVOL etc.using processes according to Microsoft Best Practices. This is meant as a disaster recovery process for small environments that did not implement backup processes and did not run a secondary domain controller that ne…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question