The problem surfaces when the stored procedure uses dynamic SQL.

using tempdb

create proc usp_test as

set nocount on

declare @sql_str as varchar(15)

set @sql_str = 'SELECT 1 FROM MyTable'

exec (@sql_str)

------------------------------------------------------------------

SELECT COUNT(*)...

Duplicate Post

The procs are actually local. I'm using a linked server to my local instance so that the returned row counts can easily be counted.