Solved

Using LEFT function in dynamic SQL call to ADSI

Posted on 2010-11-29
15
748 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 28

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 28

Expert Comment

by:sammySeltzer
ID: 34232237
Please check to see if your linked server (ADSI) allow remote accessing.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. 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 28

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 28

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 28

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 28

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article outlines the process to identify and resolve account lockout in an Active Directory environment.
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 tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
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…

756 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