Yeah that did it. Excellent! Thanks for your help.

Regards

I've also tried adding

option (maxdop 1)

to the stored procedure which is ran to avoid the parallelism but still the second time it runs it stalls in Exchange and Sleeping.

Yeah Auto Create Statistics is turned on. Cheers. Would the statistics explain why it works the first time and not the second time?

In researching it could be linked to this problem with parallel processes as that's why am getting the Exchange (Wait-Type)

http://www.dbforums.com/t656327.html

http://www.dbforums.com/showthread.php?threadid=529043

Yet when it works fine the status is Runnable and the wait type is OLEDB

Absolutely nothing is different it's really perplexing as to why it fails the second time you run it. Although looking at the processes the update processes status is set to...

That seems valid but it's identical on another server and works fine there. Does it also explain why it works fine the first time but fails the second time.

Also an...

Additional notes:

Running SQL Server 2000 (SP4)

The view i'm calling the update on is on open query views of a linked server

this is the definition, not very complex.

CREATE VIEW dbo.vw_CostPriceUpdate_Update 

AS 

SELECT     dbo.vw_CostPriceUpdate_UK.RECORD_NUMBER AS [UK Record Number], dbo.vw_CostPriceUpdate_UK.DEPOT AS [Depot UK],  

                      dbo.vw_CostPriceUpdate_UK.PART AS [Part UK], dbo.vw_CostPriceUpdate_UK.COST AS [UK Cost],  

                     ...

Also if iw as to just run a really basic stored procedure ala...

print 'hello'

or write to the events log

then this would work fine.

The stored procedure itself simply updates a column with a corresponding column in the view ala...

UPDATE vw_CostPriceUpdate_Update

SET Cost =[UK Cost]

thats all the sp does.

the strange thing is that this works...

Ok, just as i thought thanks a lot i'll create a sql job to handle this.

cheers

Yeah i thought so but was wondering if there is anything else that could be set to monitor the view, as it is pulling data in externally through openquery and...

And here

http://forums.aspfree.com/microsoft-sql-server-14/is-this-type-of-trigger-possible-on-a-view-180135.html

oh and here

http://forums.devarticles.com/microsoft-sql-server-5/is-this-type-of-trigger-on-a-view-possible-109288.html

Genius, something i totally forgot about you are a star!

Kind Regards!