Hi,
We have finally fixed this issue on our server, we tried everything under the sun. The only way we could fix this was to disable the TCP/IP protocol in the sql server settings. We have an msde version on our development box so we could not choose named pipes as the default which i believe you can in the full version. Basically i think SP2 has to use named pipes for connection otherwise it will be very temperamental. You can set these options in the configuration utitlity that comes with sql server.
Let me know if this works for the rest of you.
Thanks
Harry