Update … From Thursday, Jun 29 2006 

SQL Shaun McCarthy 9:38 pm

In the past I have quite often wanted to update values in a table based on values in another table (esp for caching data in DataWarehouses / migrating data between databases). Rather than writing a stored procedure to iterate through both tables, a much more efficient way is to use the From clause in an Update statement (NB: this is vendor specific, in this case, SQL Server). I was walking one of the junior developers through this today – basically what you need to do is start with a select statement creating the relationship between the two (or more) tables you want to combine

select 
      count(distinct r.requestID)
from
      customerRequests r,
      summaryTable st
where
      r.customerID = st.customerID

Then, you just need to change it so that it is an update statement

update
      summaryTable
set
      customerRequests = count(i.requests) — Put all the sets here…
from
      customerRequests i, 
      summaryTable st — Defines relationship
where
      r.customerID = st.customerID

Of course, you can make this more complex (in our case we had three tables updating)

 

MicroSoft ActiveDirectory Paging / Active Users Wednesday, Jun 28 2006 

C# / .Net Shaun McCarthy 2:11 pm

I got to write some code today! Yay! It had to do with ActiveDirectory – Boo!

So, ActiveDirectory can be very cryptic to work with, and that was exemplified today when I tried to implement a method to work out what users belonged to a group for sending out email. We have about 30 subscription groups here, and the email interface allows you to send to a union of them (multiple groups) and exclude other groups. The LDAP query ended up being quite complex, but I did learn two things:

  • How to check if a user is active in Active Directory
    The old code used to retrieve all the users in the groups and then perform an LDAP query on each user to find out if they are active or not. The reason this is, is that the Active flag is stored in the userAccountControl as a bitmask. Fortunately, Microsoft allow a rather unintuitive way to query this as described here. The result – add the following to your ldap query:

    (!UserAccountControl:1.2.840.113556.1.4.803:=2)

  • How to get more than 1000 results back
    If you perform an LDAP query using the DirectorySearcher object, and more than 1000 results are returned, you only get the first 1000 results. I tried setting SizeLimit to no avail! PageSize was the key – if you don’t set PageSize then you will only get 1000 results, not matter how many results there are. So, if you change your PageSize from the default (0) to 1000, it is the only change you need to make. The reason for this, is that SizeLimit limits the amount of results that you can retrieve at once – so your PageSize needs to be less than or equal to this. The paging is done automagically behind the scenes when you call FindAll() etc.

The upshot of all this? I reduced the time our emails take to generate for complex queries from over an hour to 11 seconds!

MSDN Wiki: DirectorySearcher