0

I have an OpenQuery call to Active Directory that gets the email address for a given Windows Domain account name. It works fine until I put it in an Insert trigger. The offending piece of T-SQL is:

set @sql = 'select mail from openquery(ADSI, '''
    + 'SELECT mail FROM ''''LDAP://DC=COMPANY,DC=LOCAL''''
    WHERE objectClass=''''user'''' AND objectClass<>''''computer'''' 
    AND samAccountName=''''username'''''') '
exec sp_executesql  @sql, N'@recipients varchar(500) output', @recipients output

ADSI is a linked server and I have the security context set to always use a specific user account. I get the following error returned in my web app when adding a new record.

"The member with identity 'mail' does not exist in the metadata collection. Parameter name: identity"

I have tried connecting as the same user that my app uses for connecting to the database and running the query from SQL Management Studio and I cannot replicate the problem outside of the Trigger. Can someone point me in the right direction? I suspect it is a security issue but I am lost and running out of ideas.

tr3v
  • 431
  • 1
  • 9
  • 19
  • 1
    Why you have this @recipients output parameter in your query its not being used anywhere in the query ??? – M.Ali Mar 05 '14 at 20:16
  • It is used later in the trigger to send a notification message via email. – tr3v Mar 06 '14 at 00:07
  • But you are not populating this variable inside you sql query. You need to assign it a value inside the query. – M.Ali Mar 06 '14 at 00:09
  • Humble apologies @M.Ali - I now realise you were on the right track from the start. I had initialised the `@recipients` parameter in the declaration which made me think that was working correctly when it was not. I will reinstate my original question code and if you add a reply with `set @sql = 'select @notify=Mail from openquery(...` and `exec sp_executesql @sql, N'@notify varchar(500) OUTPUT', @notify=@recipients OUTPUT;` I will then accept it as the answer. Many thanks. – tr3v Mar 12 '14 at 23:38

0 Answers0