Yeah, that's almost always the moment someone asks "Can you hotfix this issue - but only for one client" and you really wish it was a per-database view rather than...

+1 for Temporal Tables as a great solution to this kind of problem and +10 if you're on 2017 and can just set a retention policy. It's a lot easier...

There are various ways, all with there own benefits and after a while you just get a feel for it. That said, an approach that often works for for beginners...

I dunno, I'd take a slightly bloaty log file that can be robustly passed by known tools over a comma/tab delimited file every single time. Disk is cheap and working...

No worries. It's not impossible to make a design along those lines work, but it sounds like it hadn't been entirely thought through.

If you transform the data at the other end, a hash based solution is unlikely to help much. About the only way to make that work is have a bunch...

Oops, yeah I missed the sample at the bottom. If it absolutely has to have CDATA sections, it would appear there really isn't any alternative to EXPLICIT mode.

I don't believe you can pass a three part object name to sp_recompile, so you need to generate something like Use [DatabaseName];Exec sys.sp_recompile '[Schema].[Object]'

As to FREEPROCCACHE, it feels...

Do yourself a favour and don't touch XML Explicit with the longest pole you can find. It descends into an unholy, unmaintainable mess in next to no time. Use Path...

If you're on 2017, I'd strongly recommend turning on Query Store for your databases to help diagnose these issues. It really is an absolute godsend for finding and comparing variances...

Which...