Hoo-boy.
Well, first off, SM2.0 does not work well with Bioshock even if you get the thing working properly because of the fucking nonsense bullshit that they did with the transparencies. ALPHA LAYERS PEOPLE, LERN 2 USE TEM. That's directed at the game-makers. After working in the game industry a tad, you start to realize that not every decision is really all that rational, and I think this one definately wasn't. It's honestly not hard to fix, and if I can build the goddamn game's graphics on my computer, it shouldn't need a fancy card to run it neither.
However, we can fix most of your issues. Don't worry--you'll get a chance to be let down soon enough.
If you have an X800, you want this:
Shader Model 2.0 Hack for Radeon X700-X800-X850.
The important part to remember that there's several types of shader models.
Shader Model 2.0 is outdated but works fucking fine, for fuck's sake.
Shader Model 2.0 Beta 1 is less outdated and has a few bells and whistles.
Shader Model 2.0 Beta 2 is on the X700-X850 cards and is pretty cool.
Shader Model 3.0 is on the new cards and has some doohickies that you don't need, but which utterly fucking invalidates any of the previous shader models if you decide to use their specialty features without changing a goddamn alpha bit,
dammit!
Since you have an X800, give your computer a loving pat on the ass, since it's got Shader Model 2.0 Beta 2, I'm fairly certain, and should be able to run the better types of mods, the ones with the particles and shit. Honestly, the real game doesn't look all that great to justify the fucking hoops you need to jump through to play it.
Apparently these graphics were so awful that they just had to move to 3.0 and force everyone to buy a new goddamn card. OH FUCKING WAIT NO, this is the game on a fixed Shader Model 2.0 card! Wow! Water effects look pretty similar on 3.0 and 2.0, and it would run a lot faster without all the extra bullshit! Gee! Best Game Evar *I'm a smarmy asshole*?
You'll notice a few things though. First off, the walls look kinda gross--as if they were encrusted with a mixture of blood and oil and left to sit there for a few months. This is fine, this is how it's kinda-sorta supposed to look. Rapture is all beat the fuck up, but the thing is, the game's lighting is pretty ass. When you play the game on Xbox or on a ShaderModel3.0 equipped card, you never notice, since the game looks gorgeous.
However, this is not the case. The lighting is not very good. If the lighting were very good, you could merely have the wall textures look like wall textures, and let the lighting cast ominious shadows and so on to create the spooky atmosphere. This was popular back in the day with Quake 4 and Doom 3, where lighting was firmly in the game-maker's grip and people had not yet gotten tired of everything looking like it was being skullfucked by the Photoshop 'Plastic Wrap' filter.
However, Bioshock has extremely poor lighting, and in turn, has extremely good texturing to make it look like it has extremely good lighting. A lot of bloom and such, and bam, there you go. Or not, since you don't have a Shader Model 3.0 card. So your textures end up looking a little fucked up, but don't worry, that's really not abnormal. You'll notice it a lot more in the dark areas than the lighted ones, but the patch I pointed you towards fixes a lot of the transparency issues that would turn the water into black stuff--which I justifed by calling oil, which was kinda cool in my head.
So the wall textures in specific, and the floors, end up being pretty low-rez looking ugly textures due to the fact that they're no longer carrying the water of the game's lighting/particle/graphics team and it makes them look kinda chunky. Don't blame the modders though, that's the way BioShock was meant to be. I think this is important to state because you are honestly not getting a vastly DECREASED performance out of a Shader Model 2.0 card, and it does not justify going to 3.0--the fact is that they did a reacharound instead of the old fashioned way and you're just not normally able to see it.
The way to correct this is to tweak your gamma--or if you have the ability, turn down the red on your screen. The red/black is supposed to fade into the darkness and stay out of light most of the time, except via spooky ambient light sources like Big Daddies. But the lighting is all fucked, so on your copy, it won't work right--and you end up seeing some of the least worked-on aspects of the game all the time. Annoying.
The overall effect is that it ends up looking like Silent Hill a lot of the time, especially with the Slicers wandering around in nurse outfits with masks on their face. I mean, for crying out loud, how was that not obvious? Smear the walls with red and black and have some creepily scantily clad nurses wandering about? Unlike Silent Hill, though, there's no underlying message of repressed sexuality. Pity! It would have made the whole "Big Daddy/Little Sister" thing all the more interesting.