Hi opc, I saw your code. But actually I was not just seeing the results of the config statements. There are more logic after the config statements, which I didn't put it here because they are not relevant to this problem. And I am just a developer new to my current company, not a DBA, so that's why I want to make sure my code won't break without relying on other people. I ended up putting the config statements in a separate proc (say usp_config). So in my 'outer ' proc, the usp_config is called first then I can run the

insert into #temp exec 'inner proc'

Thanks.