Thanks for the replies, I was on holiday so didn't come here.

Actually in my first proc (nz_test1) I just want to make sure to turn on 'xp_cmdshell' and 'ad hoc distributed queries'. Then following queries will generate a result set.

The second proc (test_nz_tb3) willl then used the result set. So there has to be the

insert into #t

exec nz_test1

to get the result set from the first proc.

I know if the insert statement is removed then everything is fine.

So maybe I should put the 'xp_cmdshell' and 'ad hoc distributed queries' code in test_nz_tb3? Or maybe just set these separately (E.g. run it only once as a administration task), since these seem not relevant to the main logic of the proc. But if these are accidently turned off by someone else then the proc will break. I am just wondering what is the best practice. Thanks.