thanks Jeff the OPENQUERY solution solved all discrepancies.

Yes I am using four part naming and have tried to offset that by using other views which I thought was successful until it failed over time. I didn't know...

I tried changing CommandTimeout  and kickstarting the linked server and reducing some of the query without any change. I'll look for alternative methods.
Thanks.

thanks Lynn that's all do-able, that's great.

Thanks. does that mean I have to rename the files to the original when restoring back?

Thanks Drew much appreciated. I've never worked with that code before. the initial table or combination of tables has about 13 million records unfiltered table 2 has about 6k plus...

thanks, much appreciated.

Thanks. I was hoping that by now they would have something and I would not have to setup a sp.
I've never heard of injection so thanks for that.

The difference in the estimate and actual recordcount is the result of the combination of the inner join and the AND clause in the table join if that makes sense....

Joe - it might be better to do both inner joins on the asset to attribute table which returns a match of 14,538 records a 40,355 difference from returning all...

It looks like they are not MUCK tables as they leave generalisation to the application in that they use an identifier for each unique list in one large (dare I...

Thanks Grant, I like what you say it makes sense, as you can see it's ugly. I've attached a couple of txt files containing xml of the execution plan for...

this is the fastest after some changes to get rid of the distinct clause:

select cast(ar.assnbri as integer) Asset_Number, ar.long_descr, attr1.seln_code1 Main_Use,attr1.seln_code2 Diameter_mm,attr1.seln_code3 Material,

(select fdescr from SELN_CODE where SELN_TYPE = 'AM_LINEM'...

I'm still working out your diagnoses Grant, thanks for that. FYI this is an example of one of the queries with less lookups and less tables, these are all tables...

will do, thanks again.