Running SP with Access pass-through query

Got very strange behavior running SQL Server stored procedure with Access pass-through query. I have a SP with several parameters, temporary table and select statement, like:

CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)

    as

CREATE TABLE #Orders ( ID INT)

exec ('Insert into #Orders Select ID  from tblOrders Where ID ' + @FilterExpr)

SELECT ID from #Orders

drop table #Orders

 

And I run it in Access using MySP '= 1'

This query/SP worked several years. During these years database have grown, of course, don’t know if that was a reason, but once users start to get a timeout running that query more that 180 sec. Same time running MySP '= 1' in SQL Server management console worked just fine. Furthermore, there is a copy of database on same SQL server, and this SP works fine there. After some time I found how to fix it, it was set nocount on, and now my proc works fine, as below:

CREATE PROCEDURE [dbo].[MySP] @FilterExpr varchar(100)

    as

BEGIN

set nocount on

CREATE TABLE #Orders (ID INT)

exec  ('Insert into #Orders Select ID  from Orders Where ID ' + @FilterExpr)

SELECT ID from #Orders

drop table #Orders

END

I don’t know why setting nocount on help here, if anybody have explanation – please let me know. Anyway – keep this trick in mind when you writing stored procedures to be used in Access.