Emil's Wicked Cool Blog

A common pattern in applications is to have a search stored procedure work as the backend for a search screen. If a term has not been passed by the user, the intent is that there is no filter on the results for that parameter. Here's a great article discussing the ways that this can be acheived. I'm a personal fan of the COALESCE statement talked about in the static discussion.

One area that was not discussed in the article was the idea of having similar functionality with a list of filters (e.g. filter to these specific books in the library). I found a pretty good article talking about SQL 2005's use of Xml parameters to do this - Xml's main benefit being that it's more designed for this use case than a delimited string. However, the same problem now applies...what if I either have a list of books, or don't pass anything, meaning I want to search across all books?

My solution was to grab all the data from the reference table and build my own Xml string. Then I could use it in the main select statement:

IF @param is null
SET @param =
(SELECT 1 AS Tag,0 AS Parent,MyElementName AS [Root!1!string!element]
FROM sourcetable FOR XML EXPLICIT)

SELECT...
WHERE...
...AND
FieldName IN
(SELECT Field.value('.','VARCHAR(max)') FinalName
FROM @param.nodes('//string') AS Table(Field))

I'm sure this wouldn't perform well with large tables, but my reference tables are fairly small (<100 rows each), and it works great.

FYI: Here is an example COALESCE statement for the standard filters:

table LIKE '%' + COALESCE(@Name, fac.FacilityName ) + '%'

While SQL Server data update notifications through the SqlDependency object seemed like a great idea, I now believe the architecture is fundamentally flawed...I'm looking forward to changes in this area to make the feature more robust in the next release(s) of SQL Server and/or the .NET Framework. Here are some of the problems I've run into:

• Massive amounts of required permissions in the default run mode
• Query types available are incredibly restrictive
  
• Ineffective tear down of resources, again, in default run mode, with no programatic workaround. (The worst part? The problem is actually most acute while doing active development...)
  
• Severe ramifications for any issue, whether caused by environment or poor coding
  
• Complex (and maybe unusable) setup for custom service/queue implementation to alleviate the first two issues. I never really even figured out that setup when operating in a non-default schema in a restricted permission environment.

This feature is a great concept, but with an implementation that severely limits its use. My recommendation is to avoid this functionality in nearly all cases. In my current project, I've moved to a custom cache implementation that checks last modified date across all all lookup tables and clears the cache of all out of date lists. If something happens in between checks, we'll just have to deal with that.

While I like the idea of SQL 2005 Query notifications, the setup restrictions and instructions are fairly opaque. Blah! I did manage to get it working after noting all the restrictions on the query in this MSDN article, but then I made the mistake of removing dbo permissions from the user, and was thrown into the mix again for another hour of churning.

This blog post was pretty useful, but didn't go quite all the way. Later, I found a post describing more details after some searching, and came up with this set of grant statements to make it work:

• GRANT ALTER ON SCHEMA :: [schemaname] TO [Role]
• GRANT CREATE PROCEDURE TO [Role]
• GRANT CREATE QUEUE TO [Role]
• GRANT CREATE SERVICE TO [Role]
• GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [Role]
• GRANT VIEW DEFINITION TO [Role]
• GRANT SELECT to [Role]
• GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [Role]
• GRANT RECEIVE ON QueryNotificationErrorsQueue TO [Role]
• GRANT IMPERSONATE ON USER::DBO TO [Role]

After 7 years at Intel, I've have now joined Windsor Solutions, a 35 employee company focused on systems to manage environmental reporting and compliance at various state and local governments. It's energizing to be part of such a vibrant, growing organization, with the ability to wear many hats!

I ran across a problem the other day in a PHP script, where a variable wasn't getting populated. It turned out that I misspelled the variable name - clearly my fault. However, it got me thinking about the language itself, and how it is actually somewhat hypocriful. On one hand, PHP (and Javascript for that matter), is relaxed when it comes to variables. There is no strong typing, and in PHP, you don't even need to declare a variable before using it. On the other hand, both languages are type sensitive.

It seems to me that if you're going to have a laid back policy in regards to variable naming, you should also be laid back when it comes to case. Being case sensitive with weak controls on variable definition doesn't make sense. It's the computer science equivelent of mixing first and third person.

I stumbled across this problem today, and found an interesting feedback page to Microsoft regarding the issue: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=105560

Workaround:

• Regedit: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\AddIns
• Go into the add-in key you're interested in disabling
• Manually set the LoadBehavior entry to 0x0

Looks like Google just released a tool to synchronize Google Calendars with Outlook. Woo hoo! Unfortunately, it will only sync with your primary calendar, so in the meantime, I'm still using gsyncit.

I just stumbled across some interesting behavior in 7-Zip. Not sure why this is how it works, but if you drag and drop a file from 7-Zip into a directory to extract, the extract will first go to the temporary directory and then be copied over into the destination file. If you click the "Extract" button and select the directory, the file is extracted directly in the destination directory.

I had a little pen issue - a few of my good pens were wearing out. Bad pens kind of bother me. I'm too cheap to spend a lot of money on pens. I did a quick search on the Internet, came across this review, and was sold. Costco carries a pack of 12 for $15.68 online (I thought they were 11.59 in the store, though). Done deal - after about a month, I'm still very happy with the choice.

(Note: I do not participate in paid review stuff...my opinions are 100% uninfluenced).