If you read in sql server books online

"SQL Server collations apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data."

I do not know 100% as I do not work with collations often, but I think the difference between unicode and non-unicode in the scripts would make the difference because you are specifying non-unicode with varchar(max). Just my 2 cents though, that may not be correct 😀

I have noticed a few 'bugs' with using varchar(max) and nvarchar(max) causing errors, whereas using nvarchar(8000) removes the error. I have had this happen when using dynamic sql composition using exec sp_executesql @sql, @parmdef, @parm1 = @_parm1 etc. Using nvarchar(max) seemed to have had issues, but using nvarchar(4000) solved the problem which makes no sense to me still but there you go :).