Seems we could use a little more details about what needs to be done..

Is it to just add nonexisting (new) rows from table1 to table2, or is it to update...

No particular thoughts except that it seems to be permissions related in one way or another. I don't use DTS much, so I can't really say if this behaviour is...

It could be either, I guess. Take a look on the columnname in question if it allows nulls or not on your destination, and whether it also is as it's...

create table that has columns that fits what the sproc returns.

INSERT myTable (col1, col2...)

EXEC @err = myProc

if (@err <> 0) goto errhandler

The insert/execute combo is what you're looking for. The...

Sergiy got it right. You have to turn around your thinking.

Instead of attempting a (possibly fatal) operation and only after the fact evaluate if it did or did not work,...

No, not really.

Some errors in T-SQL are fatal to the batch and will abort it immediately, and this is one of them. It's not possible to trap these in T-SQL,...

You really don't need a temptable for this. While it may work now, it's unnecessary much extra work for the server, and there is a slight chance that it may...

Maybe an issue, or maybe not. ##temp_table is a global table, so concurrent executes will share the same table. Perhaps it's just as well to make it a permanent table...

They also go by the name 'derived tables'.

You can find a good explanation here http://www.sqlservercentral.com/columnists/rmarda/derivedtablebasics_printversion.asp

/Kenneth

Can't help much on the DTS part, unfortunately, but if this is a simple task like picking data from one table and inserting them into another on the same server,...

I'm sorry, it should have read like this to be (hopefully) a bit clearer..

select sum(x.c) as totalCount

from  (

        select count(*) as...

Almost there, you just need a pair of parenthesis

Declare @numRows int

select @numrows = (select count