Segue abaixo alguns artigos Microsoft que podem lhe auxiliar na solução de problemas envolvendo o XP SP2 e o SQL Server ou o MSDE:
KB 842242 Some programs seem to stop working after you install Windows XP Service Pack 2. 
KB 839278 BUG: The changes in a remote subscriber are not updated to the publisher for a queued updating replication that uses Message Queuing on a computer that is running Windows XP Service Pack 2.
KB 839269 You may not be able to connect to an instance of SQL Server that is configured to use the Named Pipes server network library on a computer that is running Windows XP Service Pack 2.
KB 839279 You may receive a 7391 error message in SQL Server 2000 when you run a distributed transaction against a linked server after you install Microsoft Windows XP Service Pack 2.
KB 873160 You may receive a 7391 error message in SQLOLEDB when you run a distributed transaction against a linked server after you install Microsoft Windows XP Service Pack 2.
KB 839280 BUG: SQL debugging does not work in Visual Studio .NET after you install Windows XP Service Pack 2.
KB 841375 A message stating that an instance of SQL Server is vulnerable to virus attacks is logged in the application event log when you install SQL Server 2000 or MSDE 2000 on a computer that is running Windows XP Service Pack 2.
Os artigos abaixo descrevem as melhores práticas para a implementação do Windows XP SP2 com o SQL Server:
KB 841249 How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server.
KB 841251 How to enable SQL Server connectivity on Windows XP Service Pack 2.
KB 841252 How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL Server 2000.
KB 841253 How to enable SQL Server 2000 Reporting Services on Windows XP Service Pack 2.
KB 842005 How to enable the ISAPI component on SQLXML on Windows XP Service Pack 2.
KB 841256 How to enable SQL Server 2000 Analysis Services and OLAP Services on computers that are running Windows XP SP2.
KB 839980 How to use a script to programmatically open ports for SQL Server to use on systems that are running Windows XP Service Pack 2.
KB 875357 Troubleshooting Windows Firewall settings in Windows XP Service Pack 2.
Finalmente, recomendo que assista ao excelente Webcast apresentado por Richard Waymire
MSDN Webcast: SQL Server and Windows XP SP2 – Level 300