Thank you for the reply Jeff.

If OPENROWSET can return only one result set, why not return it from "inside_proc", which is first statement (EXEC statement) in "ash_test" procedure?

If I place EXEC statement at the end in ash_test procedure as follows, it works fine:

SET @str2 = 'I am top most proc';

select @str1 as str1,@str2 as str2

EXEC Caspar.dbo.[inside_proc] 'I am proc inside proc'

Even here, we have two result sets, but OPENROWSET works fine printing whats in @str2.

I'm bit confused on why it fails when EXEC statement is run first,but doesn't fail when runs at the end.