Jeff Moden (4/21/2015)
Just a curious shot in the dark on this... what is the default collation of the database you're select from and what is the default collation of TempDB?
Interesting question, but both the user db and tempdb are SQL_Latin1_General_CP1_CI_AS.
Other things to consider:
1) this behavior is sporadic
2) the behavior seems to be related to the method of INSERT, not the SELECT itself
3) in addition to the other workarounds mentioned, recompiling the SP seems to alleviate this. But it shouldn't/