Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ldap query with variable Expand / Collapse
Author
Message
Posted Tuesday, November 5, 2013 2:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
I am querying Active Directory and am able to get results when I hard code the filter requirements. But I'd like to pass a variable value for displayName. Since I'm querying AD I have to use special SQL Syntax and I've successfully done so, like I said. But, as soon as I declare and set a variable and try substitution it doesn't work.
I've read here http://www.sql-server-performance.com/forum/threads/ldap-query-with-a-variable.12537/ that I need to use exec dbo.sp_executeSQL. I've followed the example, but am still getting errors about escaping quotes etc.

Can some expert please point syntax improvements to get this to run:

declare @name varchar(100)
declare @sql varchar(max)
SET @name = 'Sanjay Whitehouse'
set @sql=
n'SELECT * FROM OPENQUERY (
ADSI,
'SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title

FROM ''LDAP://domainobfuscated

WHERE
objectClass = ''USER''
and objectCategory = ''person''
and department = ''APEX*''
and displayName = ''+@name+*+''')'

exec dbo.sp_executeSQL @sql

I get this error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'SELECT * FROM OPENQUERY (
ADSI,
'.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ''.
Post #1511650
Posted Tuesday, November 5, 2013 11:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,648, Visits: 5,328
Looks like you're missing a couple of single quotes. Maybe this?

declare @name varchar(100)
declare @sql varchar(max)
SET @name = 'Sanjay Whitehouse'
set @sql=
n'SELECT * FROM OPENQUERY (
ADSI,
''SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title

FROM ''LDAP://domainobfuscated

WHERE
objectClass = ''USER''
and objectCategory = ''person''
and department = ''APEX*''
and displayName = '+QUOTENAME(@name,'''')+')'

exec dbo.sp_executeSQL @sql


Edit: Added QUOTENAME.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1511726
Posted Wednesday, November 6, 2013 10:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
yes, it's still not working. I really don't have a clue about how to solve.
Error is
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'SELECT * FROM OPENQUERY (
ADSI,
'SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
'.

Here's how it looks without variable substitution, and it returns rows:
SELECT * FROM OPENQUERY (
ADSI,
'SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title

FROM ''LDAP://redmond.corp.microsoft.com''

WHERE
objectClass = ''USER''
and objectCategory = ''person''
and displayName = ''Sanjay*''')
Post #1511957
Posted Wednesday, November 6, 2013 11:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
getting close after reading this thread http://stackoverflow.com/questions/12184670/using-openquery-to-connection-to-active-directory
Query parses this time (no error message), but without results (just column headings), which I know are there.


declare @name nvarchar(100)
declare @sql nvarchar(max)

set @name = 'Sanjay*'
set @sql=
'SELECT * FROM OPENQUERY (
ADSI,
''SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title
FROM ''''LDAP://domainobfuscated''''
WHERE
objectClass = ''''USER''''
and objectCategory = ''''person''''
and displayName = ''''+@name+'''''')'

exec dbo.sp_executeSQL @sql


--run like this it PRESENTS A BUNCH OF RESULTS:

SELECT * FROM OPENQUERY (
ADSI,
'SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title

FROM ''LDAP://domainobfuscated''

WHERE
objectClass = ''USER''
and objectCategory = ''person''
and givenName = ''Sanjay*''')


--Also, IT PRINTS:
declare @name nvarchar(100)

set @name = 'Sanjay'
PRINT
'SELECT * FROM OPENQUERY (
ADSI,
''SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title
FROM ''''LDAP://domainobfuscated
WHERE
objectClass = ''''user''''
and objectCategory = ''''person''''
and givenName = ''''+@name+'''''')'
Post #1511965
Posted Wednesday, November 6, 2013 12:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
Final solution that worked and site that helped me realize this http://www.skylinetechnologies.com/Blog/Article/1309/Querying-Active-Directory-through-SQL-Server-Using-OpenRowset-and-OpenQuery.aspx


declare @name nvarchar(100)
declare @sql nvarchar(max)

set @name = 'Sanjay'
set @sql=
'SELECT * FROM OPENQUERY (
ADSI,
''SELECT givenname ,
sn,
displayname,
samaccountname,
telephonenumber,
mail,
department,
title
FROM ''''LDAP://domainobfuscated''''
WHERE
objectClass = ''''user''''
and objectCategory = ''''person''''
and givenName = ''''' + @name + '''''<----5, yes 5 single quotes around variable!
'')'
exec dbo.sp_executeSQL @sql

I don't know why it didn't error with just 4 single quotes...perhaps it was accepting the variable as a string.
Post #1511971
Posted Wednesday, November 6, 2013 5:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,648, Visits: 5,328
Glad to hear you got it working finally.

Obviously, what I gave you was impossible for me to test.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1512043
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse