asked on

Getting User's OU from Active Directory via TSQL Stored Procedure

I have this query that works as expected and I get exactly what I want to get from AD:

SELECT SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX(',',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX(',',ADsPath)))) AS OU
FROM OPENQUERY( ADSI, 'SELECT * FROM ''LDAP:// DC=sd_corp,DC=local'' WHERE sAMAccountName = ''CROMER'' ')

Open in new window

Results in one record: OU=ScreensaverOmit.

What I want to do is this:

DECLARE @USER AS VARCHAR(10)
SET @USER='CROMER'

SELECT SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX(',',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX(',',ADsPath)))) AS OU
FROM OPENQUERY( ADSI, 'SELECT * FROM ''LDAP:// DC=sd_corp,DC=local'' WHERE sAMAccountName = ''' + @USER + ''' ')

Open in new window

I get this error when I run it using the variable: "Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '+' ". I can't figure out where the error is.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carla Romere

ASKER

That was fast and worked perfectly. Thank you.

Brian Crowe

try this...

DECLARE @OpenQuery NVARCHAR(1000)
SET @OpenQuery = 'SELECT SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)))) AS OU
FROM OPENQUERY( ADSI, ''SELECT * FROM ''''LDAP:// DC=sd_corp,DC=local'''' WHERE sAMAccountName = ''''' + @USER + ''''' '')'

EXECUTE sp_executesql @openquery

Open in new window

Brian Crowe

oops...i was a little late :-)

Carla Romere

ASKER

No worries, BriCrowe - I appreciate the help!