I just stumbled across the same problem. I think the issue is that Server.Transfer is not finding the path that you specify. It works when the browser requests it, because Kentico turns that request into an actual path internally. Thats why it works when you specify the actual path of your aspx file. What I was trying to do was something like:
Context.Items["LastException"] = Server.GetLastError();
Server.Transfer("~/Fail.aspx");
Server.ClearError();
on Application_Error in Global.asax, but I will need to find some other way of doing it. I have shelved that bit of code for now, and will come back to it later! :)