I just posted a small utility on CodeProject to check for valid stored procedures, views, and functions (in SQL Server). It’s actually a polish of some work someone else had done earlier, but if you have a large number of objects and are doing significant database refactoring, you may want to check it out.
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.
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.
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.
