A maneira mais fácil de remover diacríticos de uma string no SQL Server é agrupar a string usando um conjunto de caracteres que não inclui diacríticos, como:
SELECT 'àéêöhello!' Collate SQL_Latin1_General_CP1253_CI_AI
Isso resultará em:
aeeohello!
Isso funciona apenas se você não estiver usando uma string Unicode, então lance-a como varchar primeiro se você tiver uma string Unicode.