?
Solved

Using LEFT function in dynamic SQL call to ADSI

Posted on 2010-11-29
15
Medium Priority
?
782 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

718 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