[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Using LEFT function in dynamic SQL call to ADSI

Posted on 2010-11-29
15
Medium Priority
?
806 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
  • 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
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Let's recap what we learned from yesterday's Skyport Systems webinar.
The article explains the process to deploy a Self-Service password reset portal I developed a few years ago. Hopefully, it will prove useful to someone.  Any comments, bug reports etc. are welcome...
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

591 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