0

I have a view with one column containing domain\useraccount as an nvarchar(100). I need to convert the value in this column from domain\useraccount to Last, First Middle. I have successfully created a linked server (ADSI), and can resolve a hard-coded account name to an actual name via an OpenQuery call.

However, making the OpenQuery call from within the view is proving impossible. I've tried the following options:

  1. Use string substitution/concatenation in the OpenQuery call.
  2. Create a function to wrap the OpenQuery call, and call that function from the view.

Neither of the above options works. #1 fails because OpenQuery only accepts a pre-formed string as an argument and it isn't possible to create one (as far as I know) in a view. #2 fails because, based on the error message I got when I tried, functions called from views can't themselves contain calls to EXEC(string). #2 is actually a variation on the official workaround per this KB article:

http://support.microsoft.com/kb/314520

Can anyone shed some light on this? It almost seems like there's no way to make parameterized OpenQuery calls in a view.

Thanks!

AEberhard
  • 77
  • 4
  • Richard Mueller [has some hints and tips and samples](http://www.rlmueller.net/SQLSyntax.htm) on how to connect to Active Directory from SQL Server. Mind you: the ADO search provider is *very limited* in its capabilities and it's no longer supported - it will be gone in one of the next versions of SQL Server – marc_s Jul 16 '13 at 16:45
  • Those are all good examples, but if you look carefully none of them use parameterized queries and use of the search provider in a view isn't discussed. Do you know what the replacement for this functionality will be? Custom .Net assemblies called directly from SQL Server? – AEberhard Jul 16 '13 at 21:11

0 Answers0