SQL Cache Dependencies
May 24, 2007
Hi,
I am currently using SQLCacheDependencies to help me manage my cache in an ASP.NET application that is hosted across a web farm. This utilises the SQL Server 2005 service broker feature.
I have implemented this and is currently working but i have a few questions i want to clear up and i hope someone can assist me in that.
1/. I am starting the broker service by using the command
System.Data.SqlClient.SqlDependency.Start( <connection string> );
I currently have that piece of code running when the application starts in the global.asax file.
What i want to know is will all SQL queries that hit the database register a notification?
Is is possible to only have a selected query register a notification?
In my web application only a handful of database queries require a sqlcachedependency to be created. I dont want all queries to be registering a notification as that will place a burden on my backend server.
1/. So does all queries register a notification once the broker is started?
2/. Is there a way to only tell selected queries to register a notification?
3/. When a cache object is invalidated because of a absolute and slidingscaletimeout, does the existing sqlconnection for that service broker get disposed?
Thanks
Jim
View 4 Replies
ADVERTISEMENT
Dec 1, 2007
I need to write a code for remove dependencies between Table1.Prikey and Table2.Idand add dependencies between Table1.Prikey and Table3.Id how to write the code? please help .. thank you very much
View 2 Replies
View Related
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
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
Aug 13, 2007
Is it possible to make the execution of one job dependent on the successful execution of a previous job?
View 1 Replies
View Related
Apr 3, 2001
Is there a way for me to gather all the dependencies information on all tables and views at one time?
View 2 Replies
View Related
May 20, 2004
How can get an object's dependencies in SQL Server. For example it I have written a procedure which accesses some tables inside it then the procedure is said to be dependent on that table. Or one procedure might call another procedure and hence dependent.
Can I know an object's dependent objects from any system table. I think Oracle has a table USER_DEPENDENCIES which provides this info (I may be wrong :-().
Can anyone help ?
View 7 Replies
View Related
Jul 20, 2005
The previous post regarding dependencies reminded me of another bugger I comeup with from time to time. SQL Server allows us to rename objects via asystem stored procedure (I usually use an ADP front-end that presumably callsthe same procedure), but after the rename, I find singnificant strangeness.For instance, triggers are still connected to the tables, but the trigger textstill refers to the old table name. Views and stored procedures dependent onthe table are broken, and are not fixed by even by restarting the server,though I thought the cached plans were supposed to be dumped on shut-down,then recreated on demand later.Is there some tool or technique to rename a table and automatically detect andproperly drop/rebuild dependent triggers, views, stored procedures,relationships, and anything else I'm not thinking of?Oh yeah, and there are extended properties to deal with as well, but I guessthat's intractible unless we know what client build them (e.g. Access ADP),and what it expects to be recorded there, but I suppose it would be nice tojust dump them rather than leave them there possibly corrupted from a client'spersective.
View 4 Replies
View Related
Aug 7, 2006
Hello,
I have a table in my SQL2000 server and there's a field in this table being created by an object - with a GREEN PLUS sign (look like an APPEND query in MsAccess). How can I locate and edit/modify that (append query-like) object within my SQL 2000 - Enterpise Manager? Here's some more details:
Table: Customer
Field1: CustomerID
Field2: CustomerRegion_ID (contains the ID above & RegionID below)
Table: Region
Field: Region_ID
--------------------------------------------------------------
When I checked on the dependencies of the table "Customer" I saw 1 dependency and this dependency has 2 table depend on it back-to-back (Customer & Region).
I want to modify the "Field2" of the "Customer" table and I looked everywhere on my VB/stored-procedure codes but couldn't find what triggle the creation of the field. The only thing that I suspect that would be the append object with "GREEN PLUS" sign. Any help would be appreciated.
Peter.
View 1 Replies
View Related
Nov 3, 2006
Hi
Is there any way to know as what are all the entities or objects which are dependent upon a given Table? I wanted to know if i update a particular table in a given database on a server, what are all the other tablesobjects which gets effected because of this updation, may be because of some update triggers defined on this tabl or through cascading updates etc. Any system stored procedure which can help me to know these dependencies.
Many Thanks in Advance!
View 4 Replies
View Related
Mar 8, 2007
Hello Everyone,
I want to run the SSIS service on a few computers. Can I "only" install this service or does this service needs an instance of the SQL server database as well?
* If it does need the database, can the database be shared between various instances of the service or does each one of them need their own database instance?
* are there any particular databases which are needed by SSIS in order to execute? like master, msdb or tempdb?
regards,
Abhishek.
View 5 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
Jan 6, 2007
Hey!
I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.
To achieve this ive created te following tables (truncated for clearity):
#################### Sites ##################### ID [Int] ## ... ####################
#################### Folders ##################### ID [Int] ## SiteID [Int] ## FolderID [Int] ## ... ####################
#################### Files ##################### ID [Int] ## SiteID [Int] ## FolderID [Int] ## ... ####################
Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!
Then i set up the foreign constraints as follows:Folders.FolderD -> Folders.IDFolders.SiteID -> Sites.IDFiles.FolderID -> Folders.IDFiles.SiteID -> Sites.ID
All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)
Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?
View 2 Replies
View Related
Mar 5, 2008
Hi All, How to get all the Dependencies of a Stored Procedure? Dependencies may exists in other databases also. For Example Stored procedure "sp_Employee" exists in the DB "Employee" This SP calling the another SP exists in another DB "Organization" You help would be appreciated.. RegardsGokul
View 2 Replies
View Related
Aug 8, 2001
I want to write a query that lists all table names in the order (based on foreign key dependencies to the other tables) that the tables need to be loaded. Using DTS to transfer the data, the tables seem to be loaded in alphabetical order, which causes the load to fail on databases with heavy use of foreign keys. Can anyone offer some help?
View 2 Replies
View Related
May 20, 2008
Hi All
I am trying to get a list of all the dependencies for objects in a database.
I know about sp_depends however many of the objects are not in the catalogue that the object is in and therefore are not shown.
Any help would be useful
--
David
View 4 Replies
View Related
Jan 3, 2008
As far as I know SQL Server 2005 lists only dependencies within the same database. In many cases though there are objects in one database depending on objects in a different database on the same server. I unsuccessfully looked for a tool to list those and I ended up creating a script to do the job instead. Any improvements are appreciated, e.g. the script uses syscomments which may no longer be available in the next SQL Server version - I have not found a replacement sys.XXX table yet. Please note that you need to use a connection with access to all the SQL databases on a server and that it may take a while to run the script below if you have many databases with numerous objects in there. On the other hand these are exactly the cases when you need to know which of the thousands of objects are dependent on the table or view you are looking at.
P.S. Does anybody know where the extended properties get stored (hint - not in syscomments)? I was looking for those too, since often I document in the description of a field that it links to "DatabaseABC.dbo.TableXYZ.Field1"
Plamen Kouzov
==========
declare @searchstring varchar(50)
declare @dbid int
declare @DBName varchar(1024)
declare @command varchar(1024)
declare @ServerDBs table([dbid] smallint, DBName varchar(1024))
select @searchstring = 'DatabaseABC.dbo.TableXYZ'
insert into @ServerDBs ([dbid], DBName)
select [dbid], [name]
from master.dbo.sysdatabases
where [name] not in ('master','tempdb','model','msdb','pubs','Northwind')
create table #ObjectsFound (DBName varchar(500), ObjectName varchar(500))
while (select count(*) from @ServerDBs)>0
BEGIN
set @dbid = (select TOP 1 dbid from @ServerDBs)
set @DBName = (select DBName from @ServerDBs where dbid = @dbid)
set @command = 'use ' + @DBName + '; insert into #ObjectsFound(DBName, ObjectName) select ''' + @DBName + ''', [name] from sysobjects where id in (select [id] from syscomments where text like ''%' + @searchstring + '%'') order by 1'
exec sp_sqlexec @command
delete from @ServerDBs where dbid = @dbid
END
select * from #ObjectsFound order by 1,2
drop table #ObjectsFound
View 1 Replies
View Related
Jul 20, 2005
Anybody know if there is a system function that can be used from astored procedure that determines if a given primary key has existingdependencies? I want to make a check for this and if there are none, Iwill delete the record. If there are, I will change a field calledbitStatus from 1 to 0. Enterprise Mgr. does something like this underAll Tasks, Display Dependencies. The normal way I do it is to manuallycheck for the existance of the primary key in every dependent table.SQL 2000 server
View 3 Replies
View Related
Mar 8, 2007
Sorry to start a new thread, actually Darren answered my first question on SSIS dependencies but I have a clarification and somehow the software of this forum doesn't allow me to post again on my previous thread. (my posts disappears after some time).
My question is that if I choose to deploy the SSIS packages in SQL Server, then SSIS would have a dependency on SQL Database.
Do we know where the deployed packages are stored in SQL. This information is needed so that I can maintain and monitor the DB where the deployed packages are stored.
Also, do you think its a best practice to deploy the packages in SQL ( I think it will be good because then I can have consistent version of a package deployed on many machines... since I store my packages on one consistent DB rather than multiple file folders on each machine).
regards,
Abhishek.
View 1 Replies
View Related
Aug 7, 2006
I have packages with the execute process task. I have included the exe-file from path A using add->existing item to the SSIS-project so that it lies under miscellaneous files. Deploying these packages using the deployment utility the file gets in the deployment folder that I copy to the server. Running the package installer and installing to SQL Server all files get copied to the B path (dtspackages under sql server by default). Trying to run the package however results in complaints about the file not being at location A. I thus changed the path in the execute process task to a relative one and then I get a complaint that the file "is not in path". So how do I do to make sure it finds the executable at location B where it actually is? I thought the deployment utility would handle this on it's own?
View 1 Replies
View Related
Mar 6, 2008
Hi folks ...
I am back with another quick question here.
I was working on a table and on my absence it was dropped by one of my collegues. I restored it from the backup. The problem is it is not showing all the stored procedures and other tables under show dependencies as the original table was.
Is it going to affect the way the stored proc. depenendant on it works..or everything will be fine?
If the answer is yes...is there a way I can add the stored procedures or link it to the new table?
Thanks for reading and any insight is more than welcome.
Tanya
View 1 Replies
View Related
Sep 18, 2006
Hello,
I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).
The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.
However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.
I hope that I have explained this well enough.
I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution??
I am getting desperate, so any suggestions will be welcome
Many thanks
View 4 Replies
View Related
Dec 4, 2006
Hello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and then assign the same user to the newly inserted task.Thanks
View 3 Replies
View Related
Nov 4, 2002
I have a table EMPLOYEE in database PERSONNEL, and a table JOBS in database JOBMAN, I want to create a dependencies on the primary key EMP_NUM in table EMPLOYEE and the foreign key EMP_NUM in table JOBS, how can I do it in Enterprise Manager or any other tools?
View 5 Replies
View Related
Feb 27, 2007
Hi Foilks , I know it s easy question. I know databases but not SQL dbase. Please explain if you can.
what is "DEPENDENCIES" folder in sqlserver table mean please?
Dosston
View 8 Replies
View Related
Sep 12, 2001
Hello
I tried to execute sp_depends to get a list of Table dependencies
as when you click on "All tasks/Display dependencies" but I only get a few
not all of them.
Is there anybody who knows why I got only a part of the list
Thanks in advance
View 1 Replies
View Related
Nov 4, 2004
Hi all,
Need a big favour, I have to collect information like
TableName-> it's all columnNames--> dependent table name of EachColumn(where this column is refrenced)
I have managed to get some info like:-
select so.name as 'Table Name',sc.name as 'Column Name',st.[name], sc.length,sc.isnullable,so.crdate
from syscolumns sc
inner join sysobjects so on so.id=sc.id
inner join systypes st on st.xtype= sc.xtype
where so.xtype='U'
order by so.name
sp_depends and sp_MSdependencies is not returning me same fields..
View 1 Replies
View Related
Dec 27, 2004
Hello, everyone:
I have 40 stored procedures in a database. A table X is called by some procedures. How to check which procedure call table X, and how many procedures call table X? Thanks
ZYT
View 1 Replies
View Related
Jul 8, 2015
I found this script for dependencies and want to extend it:
I want to add the obj_typ (U, FN, P, V, TR...) for the dependency
How can I do this?
Here is the script:
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel, obj_type )
AS
(
SELECT
o.[object_id] AS referenced_id ,
o.name AS referenced_name,
[Code] ...
View 3 Replies
View Related
Jan 13, 2014
i have written query to find out column names of a table in the database .
SELECT t.NAME AS TABLEName
,SCHEMA_NAME(schema_id)+'.'+c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE OBJECT_NAME(c.object_id) LIKE '%Message%'ORDER BY t.NAME
So far i got the Expected result but what ever column i am looking i want to find out the Dependency of column realted tables. by executing the above query i am getting table name and column name. How can i get the column related dependent tables.
View 1 Replies
View Related
Feb 6, 2008
Is there a SQL statement that will delete the primary key data and data in the other tables that depend on that primary key?
View 3 Replies
View Related
Feb 15, 2008
I just have a simple question on font dependencies in reference to deployed reports. In my company, we run citrix presentation servers feeding terminal sessions out to users in remote areas.
My question is, in a terminal session, do the fonts used in the reports need to be installed on the users' local machine (who is connecting via terminal session) or is it enough to just have it on the servers feeding out the terminal session?
For example, we create a report with Arial Narrow font. Our servers hosting citrix presentation server did not have this font installed, so we installed this font on the servers (thus they're displaying correctly). Obviously the server hosting our reporting services has the font. Now a user connects to the server, is granted a terminal session, launches reporting services, report is generated with the correct font displayed, and finally prints the report.
Will the report print in Arial Narrow if the users' local machine doesn't have this font install? I remember reading somewhere that the fonts do not embed, hence the reason for installing the font on our citrix servers.
I realize this is kinda out of the scope of this forum, but perhaps someone on here uses terminal services and can answer this for me. I do plan to test this out when I get home tonight. I'd test it right now, but my computer at home isn't on therefore I can't remote home (plus wake-on-lan isn't configured) to do an actual test. Just thought I'd get some insights from other people.
Thanks in advance!
View 3 Replies
View Related
