Global Address List

[GShen]

Can I somehow access the Global Address List from SQL ? Before you start scratching your head. The old send mail function (xp_SendMail) was using mapi to find the email address. New (sp_send_dmail) needs the full smtp email address. Users will be there forever getting this correct.
I was thinking of creating an Alias table to cross reference and do some funky coding in the stored procedure. However, it will be another table to maintain, a lot of logic would have to be added to mimic finding the email address in my table, etc. I thought of appending the lengthy @xxxxxxxxxxxxx after each name which is typed in but it still would require getting the 1st letter and last name spelled correctly for each recipient. Still not the best solution.

Remember when... everything worked and there was a reason for it?

 

[ousoonerjoe]

A thought.

Create your email lookup table, and create a script that polls the Global Address Book from the Exchange Server and updates the table with the info you need. Have it execute every night. While it's not just SQL, it does solve the issue you mentioned above.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[GShen]

Thanks,
I acutally found some code to view all the email addresses in the GAL. I am writing SQL to parse a string entered by an end user to pull out mult. emails. The search takes a long time with the view (no index) but so far it is working. I still need to finish the parsing. I may take the view and load it into a table at night as you suggested and then I can index the table and it should run much faster.

Remember when... everything worked and there was a reason for it?

 

[PWise]

GShen:

Can you please post the code to view all Email Address in the GAL

Thanks In Advance

 

[GShen]

Pwise,

Put the following code in a View. Replace the xxxxxx with your DC (domain controller ) name. We had 2. One will be for your normal domain and the other for your local (eg. .com)

SELECT name, mail, sAMAccountName
FROM OPENQUERY(ADSI,
'<LDAP://DC=xxxxxx,DC=yyyyy>;(&(objectClass=group));\r\nname,mail,sAMAccountName')
AS derivedtbl_1
WHERE (mail IS NOT NULL)
UNION ALL
SELECT TOP (100) PERCENT name, mail, sAMAccountName
FROM OPENQUERY(ADSI,
'<LDAP://DC=xxxxxx,DC=yyyyy>;(&(objectClass=User));\r\nname,mail,sAMAccountName')
AS derivedtbl_2
WHERE (mail IS NOT NULL)
ORDER BY name


Remember when... everything worked and there was a reason for it?