I also have had the same basic issue several times now.
In my situation:
MacBook Pro running 10.6.1
No external drives or mounted network drives
(Only 2 USB devices plugged in: keyboard + mouse; wired ethernet, airport off)
The trigger may have been dragging an item to the trash.
Screen shot of the error:
http://tinyurl.com/yenmfot
I gradually lost the ability to do anything on the system. At first I couldn't open Finder windows or alt-tab, which means I was basically limited to the windows I already had open. I tried to get to Terminal, but all my keystrokes were going to my Firefox process. I could select things from the menubar, but there wasn't any result to doing so. Left with only Firefox I couldn't guess of a way of fixing anything.
Luckily (?) I have remote access on and could determine my IP address, so I could ssh into my laptop from another machine. I saw that Finder wasn't running, so I tried to launch it, but the command line gave me the same error:
open /System/Library/CoreServices/Finder.app
LSOpenURLWithRole() failed with error -10810 for the file /System/Library/CoreServices/Finder.app
I tried a kill -HUP of the Dock followed by an open, but that didn't accomplish anything. Not being too familiar with OSX I looked for other processes to try restarting and decided windowserver sounded like a good one. Poor choice; it basically instantly logged my account out, which isn't much better than just holding down the power button since it killed my open processes. Next time I'll try something else.