That doesn't work very well.

I can't find a way to capture the err message..

Maybe this will get you started (you'll need to translate to vba) :

Sub test()

On Error GoTo...

it would surely help if I gave you this :

CREATE PROCEDURE [dbo].[SPNTest]

AS

SET NOCOUNT ON

Select Id, Name from dbo.SysObjects order by Name

Print 'Hello World'

RAISERROR (1, 0, 0)

SET NOCOUNT OFF

GO

where's the second plan?

I wouldn't wanna try...

My guess is that it will either stop (cool), or try to rollback (another 3 hours, minimum).

Play with this :

Set reg = reg.NextRecordset

I think that you can access the errors only AFTER the last recordset has been loaded.

Hey dude, you don't need to start 5 threads for the same question. You'll get your answer, don't worry.

Sounds like homework to me...

Declare @value as int

select top 1 @value = col1

from tabl1

where (col2 = 666)

group by col1

Set @value = --do what you got to do with it.

Why do you wanna start a holly was on a beautifull Friday afternoon?

I'm not gonna be a part of this.

For now .

I wouldn't, drop the data and reinsert... it'll take less time than to do multiple queries to insert/update/delete.

I've already done something like this and a straight reinsert is much faster,...

Ya... it's so obvious (when you think about it) .

Another question... what is this supposed to filter?

AND tDocArchive.doa_batchorder%1 < 1

Can you rerun the queries and send us the execution plans

SET SHOWPLAN_TEXT ON

GO

Query1

GO

Query2

GO

SET SHOWPLAN_TEXT OFF

How many rows do you get if you remove that condition?

What can be modified?

Can you show us how you're gonna use this?

... and from my experience, and because of poorly coded application. I'd suggest you simply rebuild it instead of just tweaking it. You'll save plenty of time.

To name a few :

use "force plan"

use join hints.

make 2 different functions (1 for each set of params that need different plan).

The param trick you just learned.

Run the function...