Ok, well based on that I'm going to say that the hardware and driver are working fine. In other words, the device can receive tranmissions from the router, and Windows can access the device, otherwise you wouldn't see the network detected in the list. It has to be some configuration issue with how the system is trying to connect.
I would look at the properties for the network connection. Select "Internet Protocol (TCP/IP)". Click Properties. In the "General Tab" make sure the two radio buttons labelled "Obtain an ... automatically" are set. Click the "Advanced" button, and make sure that the only thing set in the "IP Settings" tab is "DHCP Enabled" under "IP Addresses."
I assume the connection is not encrypted with WEP, since you don't mention the system asking you for a WEP key when you connect (you should turn this on). I also assume that the router is correctly set up to work as a DHCP server, since the system connects when you remove SP2.
Do you by any chance also have a LAN adapter (non wireless) in the system? If so it should show up under Network Connections in control panel. If you do, try disabling it.