1. Have fun. There's a lot of people of differing backgrounds with plenty of creativity in the air.
2. Take pictures.
3. Talk to as many people as possible and get as much contact info as possible. Networking.
4. Bring business cards (nothing fancy) if you want to spread your contact info as fast as possible. I'd also recommend using Bump but I never tried it and don't know how many people would have it installed on their smartphone.
5. Don't do all nighters building something unless you are very interested in the team's product. All you really need is a UI prototype for presentation time. MVP can be quite minimum here; the judges just need to see a pretty face.
6. When presenting funding or revenue projections during the presentation, it's good enough to say "I don't know." That's closest to reality anyway.
7. Teams can dissolve, and you can jump around if necessary.
8. If you are a hacker and you don't have web or mobile experience, it can be a downer. Contribute as much as you can in other aspects.
9. Some of the best teams had built on top of something that someone was working on before Startup Weekend. For example, a couple of party planners came into the SW looking to get help on building a website for their kind of events. Before SW, they had already scheduled a party at a venue. Impressive.
All in all, I'd say it was worth it once for fun. I'm not sure I would do it again unless I wanted to get people involved in something I was starting (see #9).
I did fail to mention that I registered as "non-technical", although I consider myself a hybrid techie-product manager (I have a technical generalist / web sys admin-ish background). If anyone has tips for recruiting hackers to my team, they'd be appreciated :)
My hopes are to meet some cool, like minded people... have a good experience learning & building something -- I don't have any of the unrealistic expectations you warn against having.
I went to one several years ago, and I understand they've changed the way the do things a bit, so my advice may not be relevant, but...
1. Don't have your expectations set too high in terms of founding something during the SW that's going to really take off. Go in expecting a great learning and networking experience first and foremost.
2. If you're a hacker, you might want to know one of the trendier platforms (cough RoR cough) OR bring a gaggle of hackers who specialize in $WHATEVER. Otherwise, you might get stuck being the only COBOL guy in a group who's coding in Ruby, or the only Java guy among a group of Pythonistas, etc. Now that can be interesting in it's own right, but it can also be a little boring.
3. Consider doing something different... if you're a hacker, show up and don't even code, but throw in with the bizdev folks, or the designers, etc. Even if you don't contribute much, you'll learn a ton.
4. Make sure to interact with as many people as you can, and get contact info from everybody and anybody who seems remotely interesting. These people could be your future co-founders, or just valuable contacts, or just friends. Definitely expect to meet some people you'll want to keep in touch with.