GOOD Question!

I think what is happening here is that the linked server does not have his account mapped on the security tab of it (which it is needed!)

THE ODBC DSN using...

ON 4M rows yes but not on : SELECT * FROM vwBigTable WHERE ...

OR:

SELECT DATEPART(mm, K.[Date]) AS [Month], K.[KPI_Code], K.[Field_Code], K.[Field_Desc], K.[KPI_Value], K.[SubValue_1], K.[SubValue_1] * 100.0 /NULLIF(M.[MasterValue],0)  AS [ReturnPercent]

FROM [OPS_KPI_Volume_Test] AS K

LEFT OUTER JOIN

(

SELECT [Field_Code], [SubValue_1] AS [MasterValue] FROM [OPS_KPI_Volume_Test]

WHERE [KPI_Code] = 7...

if the table is big you may as well populate a computed column and create an index on it. You will not be sorry you did

Have a look:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=108278

or like someone here like to say. Time to:

exec sp_update_resume

followed by

exec sp_distribute_resume

Do you really need to do the pivoting at server side ?

it will be simpler to just write :

SELECT Country, ind, SUM(Amount) as Amt

INTO YourTable

GROUP BY Country, ind

and perform...

It has been my experience that views are usually ok and fuctions sometimes are sometimes aren't so you will need to test both!!

As for indexes yes you have to make...

Don't think so!

I'll do it also as long as you keep agreeing with mine too

what is the problem with setting those triggers on the replicated DB ?

I think the main problem here is the approach. Just create a view and use it to export the data with bcp

Just my $0.02

OR - as someone suggested above use dynamic sql

If you look at my posts I don't do this very often and I am not concerned at all with my post count