VARCHAR(MAX) was just one example. There is also a possibility that the OLE DB driver could not determine the shape of the resultset that should have been returned prior to you making the changes to remove the CTEs, and removing those and bringing the code to a form the OLE DB driver could accept could have been a coincidence. If you want more assistance we'll need to the see the code for the before and after versions of the proc.
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato