Does the SQL Server 2005 CommandNotification cache dependency work with stored procedures?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dherbstemail-ng@yahoo.com - 27 Mar 2008 13:55 GMT

I can get the SQL Server 2005 CommandNotification cache dependency to
work with inline SQL. It works in a SqlDataSource as well as with the
page OutputCache when making ADO.NET calls.

Next I copy/paste the exact same inline SQL into a stored procedure
wrapper and switch my SqlDataSource or ADO.NET code to call the stored
procedure. Now the cache is no longer refreshed after data in the
query results has been changed.

Has anyone been able to get the SQL Server 2005 CommandNotification
cache dependency to work with stored procedures?

If so, is there anything special that need to be done to enable it to
work with stored procedures?

Thanks,
David

Reply to this Message

David Herbst - 28 Mar 2008 19:35 GMT

My standard stored procedure template contains SET NOCOUNT ON. It
turns out that this is not compatible with SQL Server 2005
dependencies. After removing it, cache dependencies work with my
stored procedures.

Reply to this Message

Rate this thread:

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...