I do agree with you Jeff, I have xp_cmdshell enabled in a lot of places.  You just have to be careful with it.  It is very easy to turn it...

That is what I said it would do: "The new method above only allows 2 trips per overlap grouping.  So using my example above of trip 1, 2, and 3...

I'm not seeing duplicates... change the "order by" to be by "grouping" and you will see that it is showing you multiple overlaps (ie groups); not duplicates.  If trip 1...

do you have some sample data where it is doing duplicates?  There shouldn't be duplicates as it is doing a distinct already in the CTE...

Which is the slow portion of that query?  Are ALL of the UPDATEs and INSERTs slow and taking roughly the same time to complete?

Is this a "run-once" query or is...

Yep; xp_cmdshell would solve that, but you'd need to enable it and some auditors don't like having that enabled.

UNC paths are likely going to be the safest and most reliable...

What is your disk I/O?

One thing that can help is to change your "between" to a > and <.  Between comparisons have been known to have performance issues.

On top of...

Network drive letters are mapped per user,  not per machine.  So mapping the drive with "net use" will map the drive for you not the SQL Server Service account.  You...

The first issue is easy to fix - you are only showing "t" not "t2". So when you join things t and t2 are he ones that overlap.  If you...

• This reply was modified 5 years, 8 months ago by Mr. Brian Gale.
• This reply was modified 5 years, 8 months ago by Mr. Brian Gale.

I agree with Ken here that the application side is better suited for this.

Personally, I would refrain from using undocumented features as they can change behavior OR be removed completely...

Nice work Ken!  Your solution is cleaner than mine.  I did find a flaw with it though - if you have a gap in the dates, you can get into...

That is strange.  Generally, that error is accompanied by some other messages.  You checked both SQL instances for error messages and that is the only error message you see?  Not...

Any other errors in the log?  I imagine you are getting some error prior to that one which may help in troubleshooting and pointing you in the correct direction...

DEFINITELY not an ideal solution, but works with the sample data (plus an extra a ways in the future).  It assumes the documentID  is the order in which documents arrived,...

Nice easy question that I actually got hit with recently.  Code that had been running live for 11 years, someone reported it was missing a column.  Went digging and saw...