Cache Dependency Problem

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


ADVERTISEMENT

Unable To Connect To SQL Database 'MySite-Cache' For Cache Dependency Polling.

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

Help With Sql Cache Dependency

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

SQL Cache Dependency

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

Sql Cache Dependency With Sql Express

Jun 11, 2006

Hi,
can I use sql cache dependency with ms sql 2005 express edition?

View 1 Replies View Related

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 View Related

Service Broker And Sql Cache Dependency

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

SQL Cache Dependency With Cluster SQL Server && Web Farm

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

SQL Cache Dependency Invalidation - Can It Work On The Procedure Based Command?

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

SQL2K - Clean Buffer Cache And Procedure Cache.

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

Sys.dm_os_memory_cache_counters Vs. SQL Server:Plan Cache Cache

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

Cache Hit Ratio Vs Buffered Cache Hit Ratio

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

Job Dependency?

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

SQL Dependency

Nov 24, 2005

Hi!

View 7 Replies View Related

Sql Dependency Caching

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

Snapshot - Dependency

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

Dependency Info

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

Forward Dependency

Jun 5, 2007

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...

View 1 Replies View Related

SQL Dependency Not Working

May 25, 2007

Hello ,

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.

raina

View 4 Replies View Related

Dependency Net Viewer

Nov 29, 2006

Hi,

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?

Thanks,

Mike

View 1 Replies View Related

METADATA DEPENDENCY

Nov 9, 2007

Hello everybody,

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 :-)

Alain

View 14 Replies View Related

Dependency Analyzer

Dec 21, 2006

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.

View 18 Replies View Related

Dependency Not Created

Dec 5, 2007

--The Dependency row is not created!

-- I have a procedure "Example 1".

-- When I look at the sys.sql_dependencies I didn't find a row with Table1 dependency.

--If I write the procedure like "Example 2" the dependency Table1 exists in sys.sql_dependencies.

--

--DEFENENTLY A BUG. CAN YOU FIX IT?

--I REALLY NEED IT!!!!!!



Create table Table1 (id int);

GO

--Example 1

CREATE procedure a

as

create table #Test(id int)

Insert into #test

Select id from Table1

DROP TABLE #test;

GO

Select * from sys.sql_dependencies where object_id=object_id('a');

GO

--Example 2

CREATE procedure c

as

Select id into #test from Table1

DROP TABLE #test;

GO

Select * from sys.sql_dependencies where object_id=object_id('c');

DROP PROCEDURE c

DROP PROCEDURE a

DROP TABLE Table1

View 1 Replies View Related

View Dependency

Nov 9, 2006

Hi All,

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?

Thanks

View 4 Replies View Related

Sql Dependency And Service Broker.

Feb 16, 2007

Hi everyone,
 
                  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

View 2 Replies View Related

Sql Data Dependency With GridView

Apr 3, 2008

 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 

View 1 Replies View Related

SQL Query Help, Need To Find Dependency In SP&#39;s.

Jul 8, 2002

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.

Regards,
Vnk.

View 3 Replies View Related

SQL Query Help. Need To Find Dependency In SP&#39;s.

Jul 8, 2002

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.

Regards,
Vnk.

View 3 Replies View Related

Disc Dependency Between Instances

Jul 14, 2014

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...

View 4 Replies View Related

Dependency Network Question!!

Jul 20, 2007

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?

View 3 Replies View Related

SQL Dependency GROUP BY Fails

Jul 11, 2007

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 -



http://msdn2.microsoft.com/en-US/library/ms181122.aspx



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 ?



Many thanks

Travis

View 8 Replies View Related

Association And Dependency Network

May 2, 2007

Hello,

How do I get n and only items predicted by a specific item(s) either directly or indirectly as shown in the dependency network diagram?



For instance, the predict function won€™t work for me - because running this query on AdvantureWorks:



SELECT PREDICT([Association].[Products], 5)

From [Association]

NATURAL PREDICTION JOIN

(SELECT (SELECT 'Touring Tire Tube' AS [Model]) AS [Products]) AS t



Returns Sport-100 as a second result, although it is not predicted by any mean by Touring Tire Tube as shown in the dependency network diagram.

My query should have returned just one row - Touring Tire.





View 1 Replies View Related

ReportParameter Dependency Problem

Sep 20, 2007

Hi,

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.

Paddy

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved