2

Sorry about the title. I could not think of a way to sum up my problem in a short sentence.

In a select statement, the table has a column of Active Directory ID's. I need to use a subquery to get the full names for the ADID's. I came up with the following code to query ADSI and get what I need:

SELECT givenName + ' ' + sn AS fullName
FROM OPENROWSET(
'ADSDSOObject', 'adsdatasource'; 'myDomain\jsmith'; 'myPassword', 
'SELECT givenname, sn FROM ''LDAP://my.company.com'' WHERE objectClass = ''user'' AND SAMAccountName = ''stennell''')

That's cool, but I need to parameterize the ID, Password, and SAMAccountName value. Since the OPENRECORDSET requires literal strings, I can't concatenate strings & parameters. I must use dynamic SQL. So here's the new code:

SET @sql = 'SELECT givenName + '' '' + sn AS fullName ' + 'FROM OPENROWSET(''ADSDSOObject'', ''adsdatasource''; ''US\' + @authADID + '''; ''' + @authPWD + ''',' +
'''SELECT givenName, sn FROM ''''LDAP://us.ups.com'''' WHERE objectClass = ''''user'''' AND SAMAccountName = ''''' + @searchADID + ''''''')'

EXEC sp_executesql @sql

That works, so I wrap it up into a stored procedure. But I can't use EXEC in a query. Some google searching told me I needed to put the code in a Scalar-Valued function. So I tried that, but got this error when trying to create the function:

Only functions and some extended stored procedures can be executed from within a function.

So then more google searching told me to create a SQL CLR Function in Visual Studio. But I don't have the template all the tutorials told me to choose (SQL CLR C# User Defined Function). I have SQL Tools for VS installed, but still no templates.

I need to do something like this to get the result I need:

LEFT OUTER JOIN (SELECT fullName FROM (EXEC usp_queryADSI 'jsmith','myPassword', TIV.UserID)) AS AD
ON TIV.UserID = AD.ADID

Obviously, that doesn't work because you can't use EXEC like that. But I've hit a brick wall. Hoping someone can shed some light.

tolsen64
  • 881
  • 1
  • 9
  • 22
  • 2
    Consider rewriting the stored procedure as a function, if you want to use the results in a `select`. – Gordon Linoff Dec 05 '14 at 19:52
  • I did that. I mentioned that just under the 2nd code block. But it didn't work either because I must use dynamic sql in the OPENROWSET and the function doesn't allow use of EXEC. – tolsen64 Dec 05 '14 at 20:26
  • Can you break your process into two steps where step one puts your data into a staging table and step two calls the SP which can left join to that table? – Dave.Gugg Dec 05 '14 at 22:04
  • Yes, that's what i'm attempting to do at this time. I found I couldn't get the CLR assembly created because the System.DirectoryServices assembly is not supported in the SQL environment. It's it's not one thing it's another. – tolsen64 Dec 05 '14 at 22:31

1 Answers1

0

A question for you. What do you mean you "can't use EXEC in a query"? You can use various forms of EXEC in stored procedures. Security on our server is such that I can't easily test something like what you're trying to do, but the one thing that jumps out at me is that

EXEC sp_executesql @sql

could be

EXEC (@sql)

I know that works with most sorts of queries, haven't tried it with openrowset

EGP
  • 616
  • 5
  • 11
  • I tried to use it in a function. But SQL threw an error saying it couldn't be used in a function I posted the error message in my question above. Only Extended Procedures (CLR) can be used. But System.DirectoryServices, which is what i need to use, isn't supported in the SQL environment. – tolsen64 Dec 05 '14 at 23:03
  • Okay, I misunderstood. There is no straightforward way in SQL Server to do something like Select * from usp_QueryADSI. If it were me, I'd put it in a temp table or table variable. If you don't want to do that, you might try using OPENQUERY instead of exec in that final query. Don't know if it will work, but the second answer on this question sheds some light on it: http://stackoverflow.com/questions/18103657/view-joining-with-stored-procedure-results – EGP Dec 05 '14 at 23:37