SqlDataSource Selected Event Not Firing

I guess I'm behind the curve on this one, or I finally ran into the rare circumstances where this happens. Evidently when you're using a SqlDataSource, and you execute a select where one or more parameters is null, you get no errors and no data. Especially when you're plugging this into a larger declarative structure like a ListView.

Ran into this today when I was using the SqlDataSource and ListView to look up users based on one of three parameters, where I expected at least one of them to be null. I didn't expect the seemingly weird behavior when my ListView kept showing up empty, but the query with the same parameters worked just fine.

Props to Christopher for posting about this one, and helping me find a clue.

TL;DR - you have to set the CancelSelectOnNullParameter to false, if you want to pass null parameter values, or the query will get cancelled before it even starts.

Doing Queries Against Active Directory using SQL Server 2005

I'm doing a lot of work with Microsoft SQL Server at my current job, and this one came across my desk a week or two ago. It took some doing to finally figure out what was going on, because the setup seemed rather deceptively simple on the surface.

Four basic steps:
1. Setup a linked server to the object that translates your queries into something active directory can recognize.
2. Add security to the linked server, active directory users with permissions to query
3. The more important step that most blogs didn't cover - setup ad hoc distributed queries on the SQL Server instance you're using this linked server on.
4. Run your sql queries against the linked server using the right SQL protocol/LDAP syntax.

---

1. Setup linked server
You can use the UI to create a linked server object, but I generally prefer using sql command wherever I can so I have a file of stuff I did with notes. That way I know how to do it again, 3 years later when someone else wants to me to do it. The sql server sytnax for settting up the linked server to active directory is this:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

2. Setup security to linked server
You have to use an domain account with the rights to at least peer into active directory. You can generally test this with something like SysInternals ADExplorer. That tools and others like it are handy for exploring the hierarchy of active directory and the properties you have available for queries. Again I used sql to set this up, but you can also use the security properties of the linked server once you set it up:

EXEC sp_addlinkedsrvlogin 'ADSI', true, '<domain>\<account>';

3. Turn on the ad hoc distributed queries property
This was a tricky step to find as I said, which is why I'm adding my voice to the two tiny screams in the vast bowels of the internet. Supposedly you can turn this on in the SQL Server Surface Area Configuration wizard, but neither I nor my DBA could find it. We found an "Ad hoc queries" option that could be turned on, but this is apparently NOT the same thing. We had this turned on already on the server in question and my queries thru the ADSI linked server still failed with the error: "an error occured while preparing the query". To turn this option on use:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

4. Run your sql queries against the linked server

We needed to get information about users in our domain so we used this query:

SELECT * 
 FROM OpenQuery(ADSI,
 'SELECT givenname, initials, sn, mail, telephonenumber, displayName
  FROM ''LDAP://<server>:389/dc=<your>,dc=<domain>''
  WHERE objectClass = ''user''
   ');

Originally we were trying to get this information from our eDirectory identity vault, since we still have that ol' monstrosity left over from when it was handling everything. However, this does NOT work with the SQL Server ASDI linked server. At least we couldn't get it to work - you get this error with the returned data:

Cannot get the data of the row from the OLE DB provider
 "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to
 reasons other than sign mismatch or overflow.