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 05, 2013 2:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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 05, 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: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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 06, 2013 10:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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 06, 2013 11:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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 06, 2013 12:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
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 06, 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: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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