Yep, so in one statement it would be...

UPDATE

 CODBA.edi_history

SET

 eh_batch_reference = e2.ebr

FROM

 CODBA.edi_history e1

JOIN

 ( SELECT eh_order_id_fk, MAX(eh_batch_reference) AS ebr FROM CODBA.edi_history GROUP BY eh_order_id_fk ) e2

ON...

Thanks John - thats not quite what I need to do though...

I need to preserve the batch references of all the previous orders....it's just the 2 records that have value...

Is it slow the first time you run it, then OK after that? Or is it completely sporadic?

I was thinking that maybe you have the 'Auto Close' database option...

An SP calling a UDF is fine, your bottleneck sounds like the views in the linked servers.

You could write an OPENQUERY directly in a stored procedure and use Output...

FYI - Managed to solve this, I noticed there was a process running that had a very high 'Wait Time' value.

This was due to an OPENQUERY SQL statement against a...

I've connected to the server via terminal services & can execute the package manually there, the drivers are the same on both client & server machines.

I think I'm going...