Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using LEFT function in dynamic SQL call to ADSI

Posted on 2010-11-29
15
Medium Priority
?
791 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
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
Wouldn't it be nice if objects in Active Directory automatically moved into the correct Organizational Units? This is what AutoAD aims to do and as a plus, it automatically creates Sites, Subnets, and Organizational Units.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
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 …

972 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