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:
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?
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....
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
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.
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?
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.
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.
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.
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.
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.
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?
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.
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?
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
I am attempting to duplicate a nifty feature that one of my colleagues used:
We have parameters where a user can select a client or an individual account. There are available values for each parameter from two queries. I tried to have the available values in the account list dependent on the user's selection in the client list by passing the client parameter's value to the query for the account data set and using it in a where clause, but I get a "forward dependency" error.
The weird thing is that my colleague tried the same strategy and it worked. We together tried to set my report up the same as his, but cannot find why his works and mine doesn't. Any ideas?
His report works when deployed from my machine, so there must be something in the actual report that is different...
We have a Windows Service running on 30 server that is using SQLDependency on 10 different tables to recieve data change notifications. We have noticed that when a data change occurs not all 30 servers recieve the notification. Any help that you can provide to troubleshoot this issue would be greatly appereciated.
I've got a Decision Tree model and I'm trying to browse the dependency network. Apparently, there are too many nodes and they don't all show up in the view. I know that I can find hidden nodes, but is there a way to show all w/o having to add them one by one to view? How does the UI determine which ones to filter out?
It's now quite some time that one particular behaviour of SSIS is really frustrating me and I would like to know if I'm the only one experiencing this problem or if other people have the same problem. The issue I'm talking about is SSIS 'dependency on what is written in the XML files describing the flows. Particularly with the Data Types of columns. I'm explaining myself: Imagine your are developping a flow containing several numeric(18,0) columns... During the flow you have to perform a lookup on an Integer Field.... Of course this operation is not allowed as a numeric is not mappable with an Integer... (This is, in my opinion, a nonsense as an implicit conversion has to be possible). as a result of this behaviour, I decide to change the datatype (numeric) from my source query to an integer and use it in the Lookup which of course succeeds but now I have a second problem: each lookup in my flow has an error handling branch which I'm joining back using a Union transform. and there we have the second irritation: the Union transform doesn't replicate the Data Type changes that occured upwards in the flow... worse: it even has no interface to let you modify the data types like the advanced editor of some transforms or data sources. (I've just lost a complete dataflow while trying to modify it manually in the xml file directly :-( for those who are considering modifying directly the XML, don't!! You are asking for trouble and a lot of frustration when you'll switch back to the designer to see the effects ) My question is now: Am I misusing SSIS?? Is there somewhere an option to activate in order to get this behaviour fixed?? Has anyone else experienced this problem?? How are you solving this?? Are there any plans in the future to loose this dependency on the datatypes or at least add some implicit conversions??
Thanks in advance for your replies, suggestions,questions and other thaughts about this subject :-)
I have been trying to run the DependencyAnalyzer and it does not seem to be working correctly. The most that I can get it to do is just list the packages. I am trying to use it against packages that are stored as files and not in SQLServer. Has anyone had any luck with this executable? I don't know if it is because I don't have the full Visual Studio's installed on my machine. When I try to open the project, the DependencyAnalyzer and DependencyViewer are showing some errors. But I was able to run the msbuild to generate an executable with the path that is listed in the readme.txt file. Maybe, it is compiling ok but it is lacking the proper references.
when i do a right click on a Table and go to View Dependencies, I get the option of viewing both objects that depend on this table and vice versa, but this has to be hierachial right?
Like if there are three tables A,B,C and A is dependent on B and B is dependent on C,
I am able to see only from A to B, the relation from B to C is not coming. Am I wrong? Can someone correct me on the option of view dependency?
Can anyone let meknow how do i enable a service broker. I am trying to enable a service broker for an issuetracker application to get change of events in my database. When ever i try enabling it using the ALTER DATABASE [ databse] set Enable_Broker. it takes abt more that 2 hrs or more but doesnt show as enabled. Thanks in Advance, Pawan Venugopal
Hi,I want to add Sql data dependency in GridView. Whenever any changes in database, same will be reflected in Grid. but this should work in both Sql server 2000 and sql server 2005. And when we click on "Pause" button it should stop refreshing.
PAUSE
Index
First name
Last Name
Address
1
Xyz
Abc
Asdf, asdf
2
Pqr
Asd
Sdsa, asad
Is there any way to fulfill this kind of requirement? Please suggest me optimal solution to do this.Regards,Mustakim Mansuri
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
Hello, We had 10 scheduled jobs, which run more then 1000 Stored Procedures. I want to map the flow of dependency in Stored Procedure. Like SP 100 is dependent on SP 10, and SP10 was dependent on SP1 in our processing. If SP10 fails in job, we have to rerun SP1, SP10 and SP100 from Query analyzer. I was looking for a query or method to do find out flow of dependency in Stored Procedure.
I have two nodes in my windows cluster, and i have 5 SQL instances running there. Now i want to make sure that all instances are running on just one node. That is, if one instance fails and wants to start on the other node, all other instances must also failover. My idea was to use disc dependenies to achieve this, but i can just set a dependency on discs that belong to the same instance...
Hi, I have a question concerning microsoft data mining in sql server 05.
The thing is: when i create a new AS project in my visual studio define data source view and create model, let it be decition tree, i can see "dependency network" where i can figure how one column relate to another.
I want to use this knowledge in my C# application in order to choose right column to my next task. How do i get the result using dmx or amo?? Do you have any idea?
Hello, I have the SQLDependency working OK (i.e. the notification comes back when the data is changed), however I now want to use an SELECT statement that contains a GROUP BY as follows :-
select [dbo].[Investment].[ClientId]
from [dbo].[Investment]
group by [dbo].[Investment].[ClientId]
As you can see it is very simple and according to the restrictions placed on the statements possible for the notification it seems to fit within the specifications -
However when this is submitted it comes back 'Invalid'
Note that the field ClientId (in the above example) is *not* a nullable field as per the rules
I have tried numerous variations on the above (including adding a SUM() which is also within the rules as per the above link), but to no avail. Can anyone tell me why ?
I have a bunch of code for integrating reporting services 2000 with our application, which has been running fine for quite a while. We've recently encountered a problem and i can't quite figure it out.
There is a set of code that creates controls for entering parameter values for our reports, which takes an array of ReportParameters as an argument. We loop through the parameters and create controls as appropriate. The check to see if we need to create a dropdownlist of parameters populated through a query looks a bit like this:
if (oParam.ValidValues != null) { //Create ddl based on the list of valid values.
}
This is where i am encountering a problem. One of our reports, that has been running fine until recently is now not getting a dropdownlist created for it's second parameter. When i run into the code, i can see that this parameter now thinks that it has a dependency on the first parameter (which it does not), and the list of ValidValues is null.
If i go into the report designer and change the order of the two parameters in the report, then it works fine, the list is populated and there seems to be no dependency. If i change it back, it is broken again. I've checked the query that populates the list, it is not parameterised and is a very simple kind of SELECT A, B FROM TABLEA. I've looked in the report parameters and i can see nothing there either.
Your help would be very much appreciated here. I am running SSRS with SP2 applied.
