Alex & Access

December 17, 2013

Running SP with Access pass-through query

Filed under: Access, SQL Server — access @ 8:36 am

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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: