Dynamic SQL Search Routines

NB: I write a lot of SQL targeting MSSql at work.

Apps I'm working on inevitably have a lot of procs that return data based on combinations of parameters, either search forms inside applications, or reports with optional parameters.

A few years back I found what I've considered the definitive treatise on correctly and performantly writing sql that handles multiple nullable parameters (recently updated to include SQL 2008 information)... I strongly recomend reading both versions.

I had reason to revisit it today, and re-found my old Golden Hammer... so I thought I'd post it here so I can find it easier next time.

Select Blah
From [tables]
Where (@Param is null OR Value = @Param)

Tends to be slow, avoid indexes, scrape then filter etc.

Select Blah
From [tables]
Where (@Param is not null AND Value = @Param)

Tends to be fast, index-happy, lean and mean.

Enjoy

Security Implications of PDF's

I recently ran across Didier Stevens' blog, and was rudely reminded of how big a part security plays on the web these days.

I've never been a huge fan of PDF's, mainly due to the fact everytime I go to task manager it seems there's a zombied copy of Adobe Reader floating in there. This got so bad I now refuse to use Reader, and use FoxIt instead.

Even after adding a fair bit of PDF support to our latest app (allowing users to fill in html based forms which are then PDF'd for archival/electronic-signing), I hadn't quite grasped the full ramifications:

1. Allow user to create custom html
2. Distribute unknown custom html to other application users in the guise of a 'validated' document.

10 Minutes reading Didier's latest piece, Shoulder Surfing a Malicious PDF Author and some associated links gave me several insights/scares:

1. PDF's can run custom javascript
2. Binary streams (ie files) can be embedded and saved to disk from within the PDF
3. Incremental Versioning inside PDF's let's you monitor the development of a PDF file over time (similar to Track Changes in Word).
4. Virus Scanners have a hard time detecting malicious content inside PDF's.


My takeaways:

1. Be scared
2. Sign up for Didier's blog, it's quality, well written, relevant content
3. Why did neither security company reviewing our latest application consider the security impact of allowing user's to create their own PDF's?

Http Status Codes

In the spirit of my cheat sheets post from a while ago, I ran across this awesome diagram of the various HTTP status codes to be returned in different scenarios.

Useful to both decide what to return, and to understand the decision making process the remote end has (should?) have gone through to return you a given status.

Enjoy.

Something you only hear once a project....

... and let me tell you, it's an unbelievably sweet sound:

Test Manager: "Let's branch the code base, place a code freeze on it, and burn an install DVD labeled 'Release Candidate 1'".

After 15 months, I really can't think of a nicer thing to hear at this point. It's a concrete sign that the hours upon hours of overtime, the heartache of making the multitude of mistakes that are always made on a project, the (at times seemingly endless) stream of bugs from QA is all finally coming to an end.

More importantly, it's the sign that the client is finally going to get the application they've been promised for almost 5 years, a sign that for thousands of end users the dawn after a long night of *shudder* legacy is finally brightening the horizon.

For those that get here having been involved in the project, I cannot even begin to conceive how I could thank you enough for what you've done to get it to this point. You are more than aware of what has gone into this DVD, and even though some of you bailed early (no sinking ship metaphors please), I wouldn't have been able to write this without you.

15 months.
17 Developers.
1.8 million LOC.
Fixed Price.
On Time.
On Schedule.

Thank You.

Syncing SVN Repo's, Step by Step.

As mentioned in a previous post, I've been playing with Trac etc lately, so I've been doing some more advanced SVN work while I'm there. It's my Source Control system of choice, so I should probably learn how to use it correctly!

For posterity, here's how I've configured a sync of one SVN repo to another.... you might do this for testing an existing repo against a new tool on another machine (like my Trac VM efforts the other day), or as a warm-standby in the event of server failure etc.

Some assumptions: I'm not using Apache to run the local server, I've always used the path of least resisitance and just used svnserve.exe... either directly via the -d (daemon) flag, or as a windows service.

We're going to sync the remote (source) repo (which lives at svn://192.168.1.1) with a local (target) repo, using the built in svnsync tool. This tool doesn't like any other user changing the target repo once it's initialised, so we're only going to allow a single user access to the target.

1. Create the new local, target repository. svnsync requires a totally blank repo to start with... this means if you use Buildix and you've created a new project, you're out of luck because it initialises the repo to revision 1, due to the default folder structure. You'll need to blat the repo off disk and create it again.

svnadmin create D:\SVN_REPO

2. Configure the users/permissions on the target repo:
2.1 Change conf\svnserve.conf to include the following:

anon-access = read
auth-access = write
password-db = passwd

You can alter this as you need to, but these are the defaults... for some reason unless I uncommented these lines, my svn didn't want to honour the defaults... YMMV.

2.2 Add a user to the local repo for the sync process to run as. svnsync actually pumps the data into your repo using the normal svn API, so it needs a user to connect as. Alter passwd to include a new user:

svnsyncuser = svnsyncpassword

3. Configure the repository hooks. svnsync requires a pre-revprop-change hook to be in place, here's the simplest version:
3. 1 go to hooks
3.2 Create a couple of new files called pre-revprop-change.bat and pre-commit.bat
3.3 Alter the contents to be "return 0" <- that's a number zero
3.4 This tells the hooks to do nothing, but return success (code 0)

There's nothing to stop you doing more advanced hooks to ensure it's only the allowed user making repo changes etc if you want, but I'm K'ingISS.

4. Create a new user in the remote/source repo, using the same username/password as our freshly created user from Step 2 above.

This may not be needed, but it saves security issues around using one account to connect remotely, and another to connect locally. I have access to the remote repo, so I'm going to use it :)

5. It's time to initialise the sync!!
5.1 Stop any locally running SVN server process (apache or svnserve)... this is important
5.2 run the following command:
D:\>svnsync init file:///SVN_Repo svn://192.168.1.1/ --username svnsyncuser --password svnsyncpassword

Note that we used the file:// protocol to point to the repo, which exists at /SVN_Repo from where we ran the command (D:\). We also pass in the remote repo, and the user/pw to connect as.

If this goes smoothly, you should see "Copied properties for revision 0.".

6. At this point we can start up the local SVN Server (daemon mode, rooted at our repo):
start svnserve -d -r d:\SVN_REPO

7. Time to make initial sync, this is where all the hard work pays off!
svnsync sync svn://localhost --username svnsyncuser --password svnsyncpassword

This may take a while, as it propagates each revision from the source to the target one at a time.

To keep the repo in sync, you can either run the svnsync sync command in a bat script (using at, Scheduled Tasks etc), or configure a post-commit hook on the source repo as described at this really handy tutorial.

Remember you're dealing with Source Control, the single most important element of a software project... take backups before you start, and think before running any command that alters data to ensure you're running it on the right server etc.

Enjoy :)

Hyper-V Errors

I've been using Hyper-V for my virtualisation requirements lately, and in general it's been very smooth. I'd love the memory-sharing of ESX given I'm running a dev/test box, not a prod server, but so be it.

I just went to kick up a new VM, attached the DVD Drive, and promptly had the following spat at me:

'MachineName' failed to start. Microsoft Emulated IDE Controller (nasty guid) : Failed to power on with Error 'The process cannot access the file because it is being used by another process'.
Failed to open attachment 'E:'.


Ouch.

Nothing else on the host server was using the DVD drive, but one of the other VM's still had the physical drive assigned to it. Disconnecting it allowed the new VM to start straight away.

Attempting to re-attach the drive to the existing VM then threw a very similar error.

Moral of the story: Apparently only one(1) VM can have access to the physical CD/DVD drive at a time.

Hope this is sufficiently detailed to help out the next poor sap that runs into this.

All the small things....

... seem to drive you mad.

Note to self: svnserve.conf does NOT like having leading spaces on it's lines.

In my defence, I've spent the last 5 hours trying to get an svnsync from my main SVN repo (running on my NAS) into my new Buildix VM working so I can have a play with things like Trac without having to 'invent' commits/issues etc.

PlanSmart - A SQL Plan Visualiser with a shonky name

As a personal project, I've been playing with Linq and WPF etc in a little app for automating some of the work I do manually when I'm diagnosing slow SQL queries in my day job.

It's reached a point where I think it actually adds some value over and above the stock Enterprise Manager/SSMS experience, and I've had enough people take an interest in it that I've decided to throw it up online, where anyone can make use of it.

It's a first for me in a lot of ways, so I don't believe the code is of a quality I'd put my name to by open-sourcing it, but it is available for free.

Grab it here if you're keen.

Can't request two ASPX pages at once??

So we have some big reports in our app. Not huge, but big enough they take a couple of minutes to run.

Once we gather the parameters for the reports, we whack 'em away into the database (for auditing etc), and grab a ReportRequestID, a unique value for that run.

The requestId is jammed into session, and we window.open() our report viewer page, which grabs the ID, and runs the report for that ID.

Some clever cookie (a tester of all people) decided not to stare idly at the report screen, but to continue using the application in the background... the only problem is the postback doesn't happen until the report completes!! We don't even hit a breakpoint in Application_AcquireRequestState.

Have we hit the 2 requests per server http limitation? Fiddler says no.

Turns out the session in ASP.Net gets locked on a per user basis, for the entirety of the request...
We have the luxury of rolling over to Query String param passing instead, but heaven help us if we legitimately need 2 session enabled requests at once in the future.

SessionPageStatePersister, Drop Downs Clearing, and Magical Thresholds

Here's a ripper,

We have a fairly complex form in our ASP.Net 2.0 app. It has a few buttons on it that load various modal selection windows for different data items.

Bug raised: Fill in all the data, including using these three popup windows, hit save, and the Drop Downs on the screen clear out.

Debugging:

We worked out pretty early that those dropdowns were losing their ViewState.

Clicking any 3 combinations of the buttons is enough to repro the bug (ie 1 button 3 times), any 2 buttons is fine, as is one button twice.

Closing the selection windows rather than selecting something worked fine, so we started suspecting some sort of dynamic data updating issue, a la EventValidation.

Because we use the SessionStatePersister, some deep gut instinct made me disable this back to the default HiddenFieldPageStatePersister, at which point everything started working correctly.... hmmm....

So we know it's related to ViewState, but why this magical threshold of 3 Clicks??

Brilliant co-worker #1 wasn't happy with my (totally pragmatic) workaround of just setting that one page to use the HiddenFieldPersister, so he dug deeper.

Using Reflector, a little bit of code caught his eye:

if (
 (sessionPageState != null && count > sessionPageState.HistorySize)
 ||
 (sessionPageState == null && count > 9)
 ) {
          string name = (string) queue.Dequeue();
          session.Remove(name);
}

In other words, because the data stored by the SessionPersister is survives past the use of each page, it stores the data in a queue, and clears the old data as it fills up (as Rico said - "A cache with a bad clearance policy is another name for a memory leak").

As the code snippet above implies, the default queue length is 9.

So with our page:

1. Main Page Load
2. Popup #1 Load
3. Popup #1 Postback
4. Popup#1 Select
5. Popup #2 Load
6. Popup #2 Postback
7. Popup#2 Select
8. Popup #3 Load
9. Popup #3 Postback
10. Popup#3 Select
11. Main Page Postback

The observant will notice that 11 > 9, and so our main page's viewstate wasn't available in session when it needed it!!

Turns out it's easy to fix too, just add:

 <sessionpagestate historysize="number">

to the system.web section of your config file.


It added a few grey hairs though!

Rendering off-screen content with WPF

Lately I've been dallying with WPF, and have been beating my head against a wall trying to get some off screen rendering happening, here's the story:

I have a Grid, which programmatically gets rows/columns/children added behind the scenes.
I have wired up a ScaleTransform and a slider as per this awesome post allowing me to zoom in/out.

However I only get a variable size version of what was displayed on the screen, no new content comes into view >:(

Mass googling didn't help me, until I found somewhere that mentioned using the ClipToBounds property to _prevent_ content outside the bounds of the element being rendered.

Applying a ClipToBounds="False" to each valid element up my hierarchy has left me with data correctly being brought into view when I zoom out!

This seems painfully obvious now, but if you run into the same problem, hopefully this helps out!

Execution Plans E-Book

From the department of "useful things that come bundled with advertising", is this link to an e-book I found via my weekly Sql Server Central email.

180 pages of execution plan goodness, which is very well timed given the amount of sql tuning I've been doing lately!

Hope it helps someone :)

SQL Keyboard Macros

Life's been pretty busy lately, hence the lack of posts.

Here's one from the other day though, keyboard macros in Enterprise ManglerSql Server Management Studio...

Try this: selectthe name of a view, table etc in the text view, and press alt+F1 :)

Voila, sp_help output for the item.

These guys are configured through Tools->Options->Environment->Keyboard.

Now I just need to work out how to make sp_helptext work :)

Sherlock Holmes should have been a developer.

Don't you hate it when something innocuous sets your mind wandering along a path it won't let go of??

Triple J just had a chick describing her job as a graphic designer, and listed the following as the skills needed to be a good designer:

1. A very good attention to detail
2. A good eye for colours
3. A whole world of computer skills you pick up along the way

Q: If you couldn't suggest a technical skill, what would you look for in a budding programmer??

Nb: In these answers I'm going to assume I'm a reasonable dev... Some people may debate that, but it's my assumption :)

Maths skills? I was never horrific at maths, but it's a long way from my favourite discipline, and I downright struggled with things like Queue Theory, storage of floating point numbers (I still shudder at the word mantissa) etc at Uni.

Intelligence? I've never met a good dev who struck me as being a few stubbies short of a carton, but I'm a long way from Mensa material, and I know plenty of very good devs who are the same.

Problem Solving skills?? Definitely important, after all that's the business we're in!

Ability to Learn: Crucially important in this field, but how do we gauge it in other people short of throwing them in the deep end?

Lateral Thinking?? I think so, being able to look beyond the obvious and discover another way through the wall is crucially important.

I contest these last two points are just two facets of the same notion: An ability to reason.

The ability to observe a set of behaviours, deductively reason back to a hypothesis that results in the observed behaviours, and then prove or disprove that hypothesis is the absolute core of what we do day in and day out..... except we call it debugging!

Yet the reasoning we're asked to perform is different form that in some other disciplines, because it spans so many layers. One minute we're reasoning on an almost microscopic level...

if (a && !b || !c){}

and the next we're operating an inter-galactic level: why is this xml doc getting corrupted when we have more than 4 people in the website at a time??


Sherlock Holmes, that fictional epitome of deductive reasoning, provides a beautiful example of how his coldly analytical logic makes up for any lack of 'creative' lateral thinking in his classic quote:

“How often have I said to you that when you have eliminated the impossible, whatever remains, however improbable, must be the truth?”- Sherlock Holmes

And it's this ability to mentally discard all non-relevant scenarios that leads to the 'psychic debugging' phenomenon, where someone can beat their head against a problem for hours, ask you about it, and with only the most basic of information you produce a correct answer.

When this happens, trust me, you will look like a freaking legend!

(Bonus points for doing it from a verbal question, and providing a verbal answer pointing to a seemingly unrelated part of the app within 5 seconds of the question, with no outside assistance).

So, back to the original question: What would I look for in a pre-dev dev??

I'd look for a smart (but not necessarily genius) person, an ability to learn quickly, and most importantly with a very good ability to reason.

Sounds like the perfect description for those budding forensic analysts that CSI etc have produced, but who's job market is ludicrously small!

Todays Top Tip: Multiple views of the current file in Visual Studio

It's been a while since I've posted, mainly because work has eased off a bit, and that's mainly happened because nothing is causing myself or my guys too much  pain at the moment.

I was shown a very cool trick today though, which is especially handy when comparing two methods within a class, or where you keep referring back to a piece of code within your class:

Grabbing the highlighted bar, and dragging down the page provides a horizontal splitter, with two views of the current file, allowing you to keep one on the code you're looking at, and one where you're editing.





Enjoy :)

Javascript/Ajax Cheat Sheets

My day job at the moment basically involves looking after a dozen or so developers, and ensuring our project works out for everyone involved.

One of the best ways I can do that is to look after my devs, and to try and get them as productive as possible.

There's no doubt in my mind the best way to get someone productive is to try and place them in a situation where they can enjoy their work; either by removing the roadblocks slowing them down, or more preferably, by improving their own skillsets.

Cheat Sheets are an awesome way of doing both, as they provide access to information quicker than Google (what?? you can find something quicker than searching the web for it??), and over time the information on the sheet becomes ingrained in the person, increasng their own prowess.

I've always had a copy of the C# keyboard bindings and ASP.Net Lifecycle up on the walls, and after finding this post via LazyCoder, I can see some javascript cheat sheets appearing tommorrow.

One more thing to keep the team and I on task that bit more often.

Maybe it should be an Industry Standard??

The kiloWTF/sec that is.

Vestigial Tails

You know, little leftovers from a past age that are no longer relevant???

Like the Caps Lock key :)

I can't remember the last time I used the Caps Lock key for more than activating in-game Voice Chat in 1st person shooters.... well that and stuffing up either my password or giving me the privelege of re-writing the half a sentence I just typed aFTER KNOCKING IT while near the letter a.

Until now, because a mate at work has introduced me to the Caps Unlocker. This little baby is free (as in beer), lives in the systrayNotfication Area, and immediately turns off capslock when it's bumped.

Another one of lifes little annoyances out of the way.

Wokket

When the obvious has been ruled out....

...you're probably missing something equally obvious.

I was trying to uncompress a large (5Gb) file today, and kept getting "Insufficient Disk Space" or variants thereof.

I tried again (a 10 minute operation) after each of the following:

1. Ensure target disk has plenty of space (80+Gb)
2. Ensure source drive has enough space (10Gb)
3. Ensure temp directory has enough space (just)
4. Ensure system drive has enough space (30Gb)
5. Move temp directory to another drive with more space

I finally remembered that the partition I was copying to had originally been created for a partition-magic style auto-restore partition. FAT32 is a great choice for that sort of thing (boot time access), but not quite as nice for storing single 5Gb files >:(

A swift C:\convert F: FS:NTFS to convert the partition to NTFS without losing any data, and it's all sweet 15min later.

T

It's Blog time!

It's the end of the world, I've finally decided to start blogging.

I'm a .Net Technical Lead in Australia, currently working on a large project for a client, and looking after a dozen or so devs.

After using thousands of blog posts and articles to help debug issues over the years, I've decided to try and give something back, documenting the nasty gotchas that inevitably pop up on a regular basis (the gristle in our .net T-Bone).

I'm sure there'll be some other random musings etc as I go.

My phobia about private information on the web is still pretty strong, hence the lack of real detail here, for my privacy, and that of my employer and client(s). If you know me, feel free to say G'day :)

T

JavaScript errors from WebResource.axd when calling an aspx over HTTPS

I ran into a pearler the other day,

The project we're working on requires that user credentials only ever pass over https, but we don't want to run the entire (behometh) site over SSL for server performance reasons.

As soon as we called our login page (which has asp.net ajax validators etc) via SSL, we started getting the now familiar 'asp.net ajax javscript isn't here' type errors. Fiddler showed very few files being served compared to an unsecured request.

Googling kept turning up a fix revolving around the ServicePointManager (example), but it didn't seem relevant to my error... The penny finally dropped when I checked the Event Log of the server, and saw the errors mentioned on Jan Tielens blog, which also has the best treatise on why this occurs.

Using the ServicePointManager.ServerCertificateValidationCallback to return true instantly solved the issue.

Because I wasn't on the consuming side of these requests (as opposed to me making a ScriptService call), I hadn't run into the exception from the consumer side, which explains why it took so long for me to twig that this was in fact the fix I needed.

It took 2 days, but I chewed that Gristle down :)