>>I narrowed it down to the Routes table being the problem. Once I remove that from the SP it works properly!
>
>Sometimes even in T-SQL it makes sense to break complex query into 2 parts. In other words, you may try getting all info except Routes into temp table first and then add Routes table to the final select. Also, make sure that Routes table is indexed. Is it a wide table?

hmmm, there may be more to it than that. I had started deleting tables from the code and trying to run it again and when I removed routes it worked, but I had removed a few others before. Now I am just removing routes and I'm still having the same problem. :(

I will check the indexing, but the execution plan seemed to be happy with all the indexes.

I do not know what you mean by "a wide table". It's only got two columns and less than 100 records.

Can you give me an example of breaking this up into two, please? I've never used temp tables.