Unique Contraint/Index Based On Values
To enforce this declaratively in 2005 you can use an indexed view.

CREATE VIEW dbo.ActiveNames
WITH SCHEMABINDING
AS
SELECT Name
FROM  dbo.YourTable
WHERE Active = 1

GO

CREATE UNIQUE CLUSTERED INDEX UQ_ActiveNames ON dbo.ActiveNames(Name)