Registering SQL Cache Dependency In Web.config. Why Elsewhere, Too?
Jul 10, 2006
I have a sqlCacheDependency element registered in web.config that I figured would set the database up to deliver all notifications for all its tables:
<sqlCacheDependency enabled="true">
<databases>
<add name="MyDb"
connectionStringName="MyDbConnnectionString"/>
</databases>
</sqlCacheDependency>
I've seen docs saying that's all I need to do with SQL Server 2005, but that doesn't appear to be the case. I get the error:
The database 'MyDB' is not enabled for SQL cache notification.
To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.
Are the docs I read wrong or am I missing something?
View 12 Replies
ADVERTISEMENT
Dec 27, 2007
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
View 3 Replies
View Related
May 31, 2007
Hello all.. i'm having a major issue with my Sql Express 2005 database :(
I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla
Problem:
After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure
But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database
done alot of searching around the net and i've found this
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username
i'm using Windows Authentication how do i run this and will this solve the problem
Thank you in advance
Chris
View 3 Replies
View Related
Feb 8, 2008
I'm working off of the example shown here:
http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc
The tutorial shows that an entry must be made in web.config for a sqlCacheDependency node in web.config. When a dependency is added, they set an attributed called "connectionStringName" that references a connectionString established earlier in the web.config. My question is twofold:
1). First, I tried setting up my connection string using the connectionStrings node (my other apps use the AppSettings node), but when I try to extract the value in my code using the following syntax:
connStr = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["devConnStr"].ConnectionString);
I get the following compilation error: "ConfigurationManager does not exist in the class or namespace System.Configuration";
Am I extracting it with the wrong code? Does that setup exists in .NET v 1.4? No, I cannot use .NET v 2.0 for various reasons.
2). If I cannot use ConnectionStrings in that way and must use AppSettings, how do I set up SqlCacheDependency node to recognize that connection string?
View 1 Replies
View Related
Sep 11, 2007
Hello.I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.On my local machine everything seems to work just fine. On the testing server the cache is always null.Is cache dependency related - to the platform used? - to the number of IIS servers connected to the DB? - to the sql user used? Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops. Here is the code i wrote: public ManufacturerList GetAllManufacturers() { if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null) return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList; SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS")); _manufacturerList = new ManufacturerList(); DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null); while (reader.Read()) { int manufacturerID = reader.GetInt("ID"); Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID); if (findManufacturer == null) { findManufacturer = new Manufacturer(); findManufacturer.LoadFromDataReader(reader); _manufacturerList.Add(findManufacturer); } } reader.Close(); HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep); return _manufacturerList; } Thanks a lot.
View 1 Replies
View Related
Jun 11, 2006
Hi,
can I use sql cache dependency with ms sql 2005 express edition?
View 1 Replies
View Related
Nov 17, 2006
Hi,
Our development team is building an application using .net framework 2.0 and sql server 2005 developer edition. They are using service broker and sql cache dependency classes in their code and we are running into a problem where the database used for their application is growing alarmingly in size, ends up using a log of memory and finally drags the machine. The database machine is a developer class machine used for development purpose only.
After we did our research we found the following:
1. the tables created by the team do not increase in size.
2. the system tables created by sql server for service broker and sql dependency grows very big during their usage.
sys.sysconvgroup
sys.sysdesend
sys.sysdercv
The above three tables have the same number of records and they are equal in size for eg, at one point they were 1.5 gb each amounting to total 5 gb out which the actual user created tables, indexes, sp amount to only 500 Mb.
There were some post in other forums that suggested to use triggers to close the open conversations and also use END CONVERSATIONS in the code. We have used that solution also but doesn't seem to solve the problem.
Is there a definite solution from microsoft for this issue.
Anybody has any insight to this. Thanks for your help in advance.
View 1 Replies
View Related
May 30, 2008
I have implemented SQL cache dependency to invalidate cache whenever the result of the stored procedure gets changed. The stored procedure is written obeying all the notification rules.
Our problem:
As long as we are working on Local machine, Development server or Pre production server its working fine means the cache gets invalidated whenever there is any change in the result set. But the same implementation doesn’t work in Production. Cache does not get invalidated.
Difference in Production environment and other environment:
Production uses cluster SQL server, others use standard SQL server. Production has load balancing means application is deployed on 6 servers which hits same clustered SQL server. In application’s global.asax file i have Start dependency so I see 6 different notifications started in SQL server which is right. But whenever there is a change in result set the cache doesn’t get invalidated in any of the application.
My question is
Do we have to follow any different kind of implementation of SQL cache dependency for web farm and cluster SQL server scenario?
On the same note I would like to add, on the same SQL server I have one more database and a different application is accessing this database. This application is also using SQL cache dependency and its working fine. The only thing this .Net application is NOT deployed on web farm. Its deployed on single application server.
View 2 Replies
View Related
Dec 19, 2005
I was wondering if SQL Cache Dependency would be in fact invalidated if:
1. it was created based on a procedure type command.
2. if the select statement retrieves the data from multiple database tables
Any help would be more appreciated. I am stuck with the fact that none of the data bases on sql dependency is invalidated. I spent literally hours to understand what i am doing incorrectly.
Thanks
View 1 Replies
View Related
May 26, 2000
Which will perform better?
(4) 550Mhz Xeon with 1MB cache and 2GB of RAM
(2) 550Mhz Xeon with 2MB cache and 2GB of RAM
(2) 550Mhz Xeon with 1MB cache and 4GB of RAM
Thanks,
John Fletcher
View 1 Replies
View Related
May 22, 2014
I have a virtual server (VMware ESX) with 64GB RAM running a single instance of SQL 2012 SP1. The max memory config is set to 59392 (58GB).
The Page Life Expectancy for this server has been averaging well under 10 mins for the last few days, according to our monitoring.
I have been checking the amount of data in the buffer cache periodically during the day with the below query, which seems to show that there is never more than about 10GB of data at any one time, frequently dropping below 5GB:
SELECT COUNT(*) AS BufferPages,
CONVERT(decimal(10, 2), COUNT(*) / 128.0) AS BufferMB
FROM sys.dm_os_buffer_descriptorsWhy would the amount of cached data be so low (and cause so much churn)?
I am aware that other things will require some of that memory (plan cache etc.) but with Max Mem of 58GB, I would expect there to be a much higher amount of actual cached data at any one time. I did the same checks on another VM with the same amount of RAM/Max Mem setting, and there was 50GB of data in the cache, with PLE measured in hours.
View 9 Replies
View Related
May 31, 2007
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
View 1 Replies
View Related
Feb 15, 2008
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
View 2 Replies
View Related
Sep 2, 2015
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. Â My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.Â
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
View 2 Replies
View Related
Mar 13, 2003
On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?
Any help or advice would be much appreciated.
View 5 Replies
View Related
Aug 14, 2007
Is it possible to make the execution of one job dependent on the successful execution of a previous job?
View 2 Replies
View Related
Nov 24, 2006
Hello, I have caching enabled in my application and I am using SqlCacheDependency to cache my tables. Well, the cache works fine but when the table is updated the information in my cache does not update. I DO have the broker service started and running but not sure what else i am missing. I am using sql server 2005.
View 1 Replies
View Related
May 8, 2001
Does anyone know what SQL Server uses to identify other servers which contain SQL Server, when you attempt to register a new server through enterprise mgr? For instance when you go through the new sql server registration wizard, the second screen allows you to choose what server you would like to register, and it shows you the available servers on the lan that contain sql server 6.5 or 7.0. What is enterprise mgr using to locate the other servers containing SQL Server? Thanks in advance.
View 2 Replies
View Related
Dec 29, 2000
Hi,
I've problem in replicating data thru SNAPSHOT as I've the tables with Foreign Keys at subscriber end. The Truncate table is not working because these tables
were referenced by a FK. Even for recreating table during snapshot is also same problem. Any suggestions?
Thanks
TT
View 1 Replies
View Related
Mar 2, 2000
Can anyone give me advice on how to register 6.5 servers using SQL 7.0. Every time I try and register a 6.5 servers it fails. I get an error stating that "I must upgraded the 6.5 server or later to be administered by this version of SQL-DMO". I can't upgrade the server to 7.0 because the COTS product that we are using as the front end relies on 6.5. Is there any way to do this? Or do I have to install 6.5 on my workstation as a separate application than 7.0? Someone please help?
View 2 Replies
View Related
Dec 15, 2000
I just created a new db on a programmer's machine. Now I cannot register his sql server on my sql server. It doesn't like 'sa'. I can connect as 'administrator' but then I cannot do anything. Seems like the logins are messed up. I am not able to execute any stored procedures as administrator.
Any ideas besides reloading sql?
Thanks.
View 1 Replies
View Related
Oct 14, 1999
When using MSDE or SQL7 as the back-end to Access2000 projects (adp),
the examples often have an .mdf file to match such as say NorthwindCS.mdf
The instructions are:
1. set this file aside,
2. create a database with this name then
3. stop SQLServer,
4. copy own file to msql7data...
5 use it....
although this soemtimes works, i mostly get a "suspect" database
I was hoping to fix this with DBCC REPAIR, but support for this command seems
to have disappeared
Help
View 1 Replies
View Related
Aug 28, 2000
Hi,
I mistakenly registered a SQL 6.5 server in SQL 7.0. I would like to remove the registration entry, since I often will click it by acident, and it will fire up the SQL 6.5 EM session. However, when I right click on the server and try to select the "Delete SQLServer Registration" from the menu, that option is greyed out. Is there any way I can remove this SQLServer somehow?
Thanks,
Joe
View 1 Replies
View Related
Jul 24, 1998
Hi! Just started playing, but when I try to register an active (on the network) 6.5 server in the 7.0 EM I get the message that I must upgrade the 6.5 server...I know in 6.5 I could access 6.0 servers after runnning a script on the 6.0 server, but so far I don`t see anything like this for 7.0. Will I end up having to run 2 versions of EM on a machine (if possible) to access my 6.5/7.0 servers? (I know I won`t be able to do anything fancy, but it is nice (under my 6.5 EM) to be able to manage server activity/db`s from one machine, regardless of them being 6.0 or 6.5!
John
View 1 Replies
View Related
Mar 8, 2001
Hi,
I have recently installed SQL Server 2000, And trying to register a remote SQL 7 server, through enterprise manager which fails with the following error message: Timeout expired
I am able to connect to the same server using Query Analyzer.
I was able to register this server from the same machine, prior to the installation of SQL 2000. Also, I am still able to connect to other SQL 7 servers, with SQL 2000 enterprise manager.
Any Ideas?
View 2 Replies
View Related
Sep 30, 2002
Over the weekend we changed ISPs on our SQL Server 2000 machine. The only configuration change was the IP address. I'm able to register the server from client machines using EM which are on the same network, but I can't register it from home. I've verified the IP address, username/password etc and the connection just fails with the message 'Sql Server does not exist or access is denied.' Could this be a port issue? Any ideas? Thanks.
View 4 Replies
View Related
Mar 15, 2001
How do I register servers in Enterprise Manager or access them in Query Analyzer in non-trusted domains via TCP/IP? I must administer a number of servers for which I have all the security credentials (IP addresses, Windows Administrator logon and password, sa password).
View 1 Replies
View Related
Jan 12, 1999
I need help with a problem that is driving me nuts and should be so easy that it is mindless. The problem is with registering SQL servers in Enterprise Manager.
The network is a routed environment with many subnets. TCP/IP addresses are assigned by DHCP and name resolution is done via WINS. The SQL clients are set to use Named Pipes as the Net-Library. The servers are using standard security. I have the correct sa password. Then I try to register a SQL server. It returns a message that it is unavailable.
The SQL box is visible in Network Neighborhood; is able to be pinged both via machine name and IP address; the msssqlserver service is running on the box. I can register the box using standard security and the same credentials in EM right on the server. Why can't I register it from a client?
Thanks in advance,
Ed
View 2 Replies
View Related
Aug 25, 1998
is it possible to register an SQL server any other way than SQL Enterprise Manager?
If so, how?
Preeti.
View 1 Replies
View Related
May 8, 2004
Hi,
How to register a Remote SQL SERVER from a node in Local Area Network?
View 1 Replies
View Related
Jul 23, 2005
I am under the impression that either there are deficiencies in the way SQLServer 2000 is keeping track of depedencies or perhaps the dependenciessimply need to be re-evaulated / recompiled.What is the best and/or easiest way to ensure that the dependency info isaccurate?Is there a way to tell MSSQL to rebuild/reexamine them all?Thanks in advance to the MVPs and other generous and knowledgable posterswho have helped me recently.Chad
View 2 Replies
View Related
Dec 12, 2005
Please pardon my ignorance, but I am using MS SQL2000...On system start upthe Server Service Manager defaults to server "BACK" which is not evenregistered in the SQL Enterprise Manager. Enterprise Manager shows only oneserver " OFFICE" which is the one I want to default to. How do I remove the"Back" server completly from the system?thank you
View 3 Replies
View Related
