The problem turned out to be a syntax error in the trigger. I don't know why this would manifest itself in failing when run through ado but not when run...

Thanks for the advice. I have split the function into 4 each returning a smaller SQL string which is concatenated before being run by EXEC.