Firt thing.
Don't send emails from a trigger.
Ever.
Too many things can go wrong with email related plumbing, and any one of them will cause your system get frozen...

How about another approach?
You may create not only #tables, but #procedures as well.

drop procedure testmain
go
create procedure testmain
as

exec #test1_variant;

go

Why is this a temp table?
Is it a "materialized" view (join) of several separate tables?
If it's the case, then you's probably better querying joined tables directly.
Assuming, of...

One of the longest queries I've ever caught started with...
Any guess?

WAITFOR DELAY '00:30:00'

:hehe:

There is Equipment is stock, and Equipment in Order.

Equipment in Stock may have 1or more Prices, defined for different sale conditions.
Equipment in Order must have a single...

Why do you need such a long SP code?

This code will be functionally identical:

CREATE PROCEDURE [PrepareStageTables]

(

@TruncateStage bit

)

AS

IF ( @TruncateStage = 1)

DELETE [MPNM]

FROM [NolMatch] AS [MPNM]

JOIN [Devices] AS [lastload]

ON [MPNM].[TableID]...

Print @query and try to execute it in SSMS, without bcp part.

Should it look like this?

UPDATE S
SET ID_Request = R.ID_Request
FROM Student S
INNER JOIN Request R ON S.ID_Student = R.ID_Student
where S.ID_Request is...

By the logic of the request you should be looking not for the next occurence, but for the previous one.

Using John's harness I quickly baked this solution:

Is SSIS on the same server as the SQL Server instance?

A query may take long to complete just because a log file being expanded.

Or because a bulk load executed at the time applied a tablock.

Long completion time does not always...

FridayNightGiant

When you scale up - those extra 4 instances aren't materialised from a thin air, right?

They must be there, and they must be vacant.

So, someone must have purchased them earlier,...