Data Caching and Command Notification

I would like to use some of the new features of SQL Server 2005, but these guys are telling me that they won't support it.

Has anyone gotten ASP.NET SQL Cache Invalidation to work? Apparently I need to enable Command Notification on my database first with the following statement but I don't have the required credentials to run it:




USE MSDB
GRANT SEND ON SERVICE::
[http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService]
TO GUEST


USE MASTER
EXEC sp_configure 'clr enabled', 1
RECONFIGURE


ALTER DATABASE SQL2005_MY_DATABASE_NAME SET ENABLE_BROKER

Post Edited (pmarangoni) : 7/16/2007 2:43:34 PM GMT

 

Unfortunately what you are trying to ask for will require that the security settings on the DB server be lowered. This will adversely affect everybody on that server. From what I've seen unless you are on a dedicated server no hosting company that is offering a shared hosting environment will offer this feature.

What exactly are you trying to achieve with your web application. Give us some of the conditions you are trying to meet.

 

I just wanted to take advantage of one of the greatest things since sliced bread in SQL Server, that's all...

I have some queries that take a while to load and this would allow me to cache the query and have SQL Server let me know when my cache needs to be refreshed. Until SQL Server tells me that my data has changed, I can just use the cached version and have better performance.

 

Have you considered the SQLdependency class.

Discountasp should support SQLdependency. You can reference this link from our forum more more info.

http://community.discountasp.net/default.aspx?f=24&m=9653

For some more documentations on the sqldependence class, please refer to this link. http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

 

The SQL statement you are running was trying to enable CLR which we do not allow on our shared hosting environment.




USE MASTER
EXEC sp_configure 'clr enabled', 1
RECONFIGURE


The thread http://community.discountasp.net/default.aspx?f=24&m=9653is not enabling CLR but still uses the SQLDependency method to configure command notification with asp.net.

 

That's exactly what I was referring to.The documentation I've been reading suggested that I needed to run the SQL statements I posted above, but I'll just try doing this and see what I get.





Thanks!

 

The problem is that I'm using an ObjectDataSource not a SqlDataSource, so I guess this won't work.

 

You can use this feature with ObjectDataSource too.

See :http://www.asp.net/learn/dataaccess/tutorial61vb.aspx?tabid=63

Vikram

DiscountASP.NET
www.DiscountASP.NET

 

My query and schema are fairly involved, so it looks like it would take some work to get this type of caching to work the way I need it to. I don't want to use any sort of polling approach either.