November 5, 2013 at 2:54 pm
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 ''.
--Quote me
November 5, 2013 at 11:41 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 6, 2013 at 10:42 am
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*''')
--Quote me
November 6, 2013 at 11:22 am
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'
'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+'''''')'
--Quote me
November 6, 2013 at 12:11 pm
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.
--Quote me
November 6, 2013 at 5:33 pm
Glad to hear you got it working finally.
Obviously, what I gave you was impossible for me to test.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 10, 2018 at 6:21 am
polkadot - Wednesday, November 6, 2013 12:11 PMFinal 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, titleFROM ''''LDAP://domainobfuscated''''WHERE objectClass = ''''user'''' and objectCategory = ''''person''''and givenName = ''''' + @name + '''''<----5, yes 5 single quotes around variable!'')'exec dbo.sp_executeSQL @sqlI don't know why it didn't error with just 4 single quotes...perhaps it was accepting the variable as a string.
It helped me, to.
Thank you, so much!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply