j.miner (4/4/2013)
If I take your code and replace the object name with the BOL sample, it does not work.
That's a fair comment, and one I should have explained. As a standard, all our "import" tables follow the notation of [source system]_[source table name] to allow for easy identification. In this case, I'm simply splitting info to two columns. So it works for me, it's not necessarily going to work for you, commenting out those two columns still causes the same issue.
j.miner (4/4/2013)
Taking a quick look at your post, it looks like that field is in another collation sequence / code page.
Well that was my initial reaction, but one I couldn't explain because this code has been running on the same server for about 7 weeks. In addition, would that not mean it would return this characters EVERY time I ran it? This is what has baffled me here. Will do some digging on Server / Database / Column collation settings.
j.miner (4/4/2013)
I would do a DBCC on the database to make sure there are not system issues with the database.
🙂 Great minds think alike! Did this straight away, no issues to report.
The thing that has thrown me is the randomness, it happens once every 15-30 times and only then SOME columns and only then just the first 4 characters!
One thing I did do today, is back the database up and restore it on a DEV server to run the same code and it had no issues. I am resorting to rebuilding all databases affected and rerunning the process from scratch EDITED TO ADD: I will however rename the existing database and retain it so at least I can recreate (and therefore continue to troubleshoot) the error.
Will update on the collation settings...