Sql Server 2005 Notification-based Cache Invalidation

Jan 6, 2006

I'm trying to set enable SQL cache invalidation on a webpage, specifically on a a datasource that fills a GridView. 

The website http://beta.asp.net/QUICKSTART/aspnet/doc/caching/SQLInvalidation.aspx makes it sound so very simple, and I have attempted to do all that it says, yet something just isn't right.  The site says that the following needs to be done.

Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with Sql Server 2005 query notifications.
Table names in queries must include the ownername. For example, if you issue a query against the authors table in the pubs database, the query must reference the table as "dbo.authors".
The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.

I've done items 1,2 & 3, but when I attempt to do item 4 I get the following result in TSql:

Msg 15151, Level 16, State 1, Line 1

Cannot find the service 'SqlQueryNotificationService', because it does not exist or you do not have permission.

My login account has sysadmin priviledges, so I'm guessing the latter part of the error doesn't apply to me.  But what do I have to do to get SqlQueryNoticationService to exist?

 

 

 

View 3 Replies


ADVERTISEMENT

How Can I Check The Notification-based Cache Invalidation With My SQL Server 2005 Really Works?

Mar 31, 2006

I have used the notification-based cache invalidation with my web application, but how can I check it really works? I mena if the backend data didn't change , the data will come from cache not from the database?
 
 <asp:SqlDataSource ID="SqlDataSource_WebInfo" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"                    SelectCommand="SELECT simplified, traditional FROM temp" ProviderName="System.Data.SqlClient" EnableCaching="True" SqlCacheDependency="CommandNotification">
</asp:SqlDataSource>
 
Thanks a lot!

View 1 Replies View Related

Sql Cache Invalidation For Sql Server 2005

May 22, 2008

If I simply want a db table to have caching enabled and just care about the application getting that data from the cache instead of physically getting it from the table (no need for me to have a delegate for notification), there is no need for me to create a sqldependency object within my middle tier method associated with my command, correct? I have the sql cache enabled for a particular db table and have the following in my Application_Start event of the global.asox file (SqlDependency.Start “connectionstringâ€?) Once I change something in the table, the cache is invalidated. That’s all I need, correct? 
 

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

Problems With SQL Server Cache Invalidation

Jun 5, 2007

Hi I am using SQL Server cache invalidation with caching in my application.
I have a master page and several other pages that are referring the master page.
I have specified :
<%@ OutputCache Duration="60" VaryByParam="*" SqlDependency="CommandNotification" %> on one of my content page.and I have included the
System.Data.SqlClient.SqlDependency.Start(connectionstring) in my Application_Start.My web.config contains this section as well -
<caching><sqlCacheDependency enabled="true">
<databases><add name="BizPartnerV4" connectionStringName="BizPartnerConnectionString"/>
<add name="DirectBuyBeaverton" connectionStringName="DirectBuyBeavertonConnectionString"/></databases>
</sqlCacheDependency>
I</caching> have also run the aspnet_regsql utility to enable sqlcache dependency for the database.
But my page is not taking the values from cache when the page is refreshed.
Please help.

View 6 Replies View Related

Cache Invalidation

May 8, 2007

Hello,
Am wondering if someone can give me a pointer on where to start with a query i have.
What i am trying to do is provide some sort of information to a user that data from a database has been changed. I know that you can cache a page and have output cache directive invalidate it next time it loads if changed but wondering if its poss to be told its changed without reload ( dont see how but never know) - even if not on that page.
What im kinda thinking is something like the popup that outlook has when a new message arrives in in box. Is that just sending a new query? is there a table last modified function in sql? etc.
This is for a web app in c#.
Any help would be appreaciated.
cheers

View 3 Replies View Related

Service Broker Used With ASP.NET Cache Invalidation

May 24, 2007

Hi,

I am not sure is this is .NET issue or a problem with the SQL Service broker so i hope you can either point me in the right direction or make suggestions to solve this problem.


I am using the SQL Server 2005 service broker to manage my caching in an ASP.NET application. The service broker is started with all require queues and permissions set up for the service account that connects to SQL Server. The application + broker service works fine in that if a table row change is made to a registered query the cache will invalidate in the web application.

The problem i am getting is errors appear in the application event log as well as the SQL logs and i am not sure what it means or why it is throwing an error even though the application works as it was designed.

Below are a couple of errors that are being logged

The query notification dialog on conversation handle '{ADC34B70-BD09-DC11-AFEA-0016E6539AEB}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.


The query notification dialog on conversation handle '{E8642B09-BB09-DC11-AFEA-0016E6539AEB}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-f19bedef-a9f9-46e1-8f37-1b573614bd26&apos; because it does not exist.</Description></Error>'.


Service Broker needs to access the master key in the database 'test'. Error code:25. The master key has to exist and the service master key encryption is required.



Thanks for your help.
Jim

View 1 Replies View Related

SQL Server 2005 And Query Notification

Jun 9, 2006

I have been testing SQLDependency and I have a two questions;

1) When using a stored procedure to run the dependency query, using a "clean" procedure (containing nothing but a select statement), works fine. Adding try..catch (which is a part of our coding standards), results in the subscription firing immediately because of an invalid query. Is this by design? If so, how can I implement error handling.

2) How does SQLDependency handle SQL Server restarts?
I see two options:
a) The subscription is fired listing a server restart message in the related service broker queue, but as far as I can tell, SQLDependency has no way of handling these messages given the connection has been broken. (Establishing a new connection and dependency won€™t read the said messages).
b) The SQLDependency listener on the client raises an error for the connection being terminated. Can I relay on this event to recreate a connection and dependency?

Help will be appriciated
Boaz

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

Setup Email To Notification From SQL Server 2005

Aug 18, 2007

Hi,
My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.

Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?

Please help me,

Thank you very much,



sp_configure 'database mail xps', 1
GO
reconfigure
GO

-------------------------------------------------------------
-- Database Mail Simple Configuration Template.
--
-- This template creates a Database Mail profile, an SMTP account and
-- associates the account to the profile.
-- The template does not grant access to the new profile for
-- any database principals. Use msdb.dbo.sysmail_add_principalprofile
-- to grant access to the new profile for users who are not
-- members of sysadmin.
-------------------------------------------------------------

DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
SET @profile_name = 'TestProfile';

-- Account information. Replace with the information for your account.

SET @account_name = 'vdang';
SET @SMTP_servername = 'smtp.cgdnow.com';
SET @email_address = 'vdang@cdgnow.com';
SET @display_name = 'Vinh, Dang';


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ;
GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;

IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;

IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

View 3 Replies View Related

Notification To SQL Server 2005 Agent Job On Completion Of Oracle Package

Dec 21, 2007



Is there any built-in way of kicking off a job on SQL Server 2005 Agent whenever a package/job completes in Oracle?
Are there any (Triggers? Msft queue? Event Notification?) mechanisms to automate running a job on the SQL side?
Any article or knowledge articles would be appreciated also.

If not are there any built-in stardardized polling techniques? Or are there any timers in SSIS?
That way I can delay executing a child package until a certain record has been inserted into a control table in Oracle.
I don't want to write an inefficient for loop that blocks all other processing on the server and iterates once every second.

Thanks

View 1 Replies View Related

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

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

SQL Server 2005 Row Based Security?

Jul 3, 2007

Hi,

I wonder if SQL Server 2005 supports row based security?

I need to set some users to see data filtered by a specific field and value...

Example: User XPTO only sees data about vendor code = '123'

Is this possible in the box?



Best Regards,

View 1 Replies View Related

Display Analysis Services Server-Based Formatting In RS 2005 Report

Jul 18, 2007

I'm looking for a new way to publish reports based on OLAP data and was very disappointed to find that Reporting Services does not natively support displaying MDX query results in a matrix. While it is possible to assign an MDX query to the matrix control in Reporting Services, the two main problems are that the columns of the query must be measures (not dimensions), and it does not support display of server based formatting (fore color, back color, and font flags).

Does anyone know of any custom control that properly deals with Analysis Services data?

Thanks for any info you have!

View 13 Replies View Related

SQL Server Vs Cache

Apr 2, 2004

I'm very confused about which dbms to use. I've two choices: MS SQL Server 2000 and Intersystems Cache. Although Cache is post relational I don't know anything about it. However it has object oriented features, I know how to do things in SQL Server.
Can anyone give me the advantages and disadvantages of both systems in this situation?
Thanks in advance.

View 14 Replies View Related

SQL Server Cache.

Dec 4, 2007

Hi all,

We need few clarifications with regard to the caching in SQL Server.

1. When does the SQL Server clears the CACHE or at which event the CACHE gets cleared (specifically execution plans)?

2. Is there any way to retain the execution plans in the CACHE without clearing them ?

3. Can we rely upon the SQL Server CACHE for better performance or the system? We have a stored procedure which takes 25 sec and when the execution plan is cached it takes only 5 sec. So can we rely on execution plan caching in the production environment?

Thanks & Regards,

Hari Haran Arulmozhi

View 2 Replies View Related

Notification From Server

Apr 30, 2001

hi,

An application, every one hour it connects to sqlserver and uploads data from MSAccess database.i should get the notification weather that application is connected to the server or not.because this application gets currepts very often.

any help is appreciated.


thanks
preethi

View 1 Replies View Related

SQL SERVER NOTIFICATION

Sep 17, 2007

hi i am getting the error for granting


GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sa

error is

Cannot find the user 'sa', because it does not exist or you do not have permission.

SO PLEASE GIVE ME THE CODING

step by step for notification


thanks

View 1 Replies View Related

Sql Server Notification Service

Dec 13, 2004

Hi,
I'm trying to install Sql server notification service on my pc, which has sql server 2000 and vs.net 2003 installed, but its telling me that I should have the .net framework installed before continuing the installation. The .net framework is already installed, any ideas why is it doing this????

Thanks

View 1 Replies View Related

Sql Server Notification Services

Dec 12, 2007

Hello
This is first post to this forum
I have read that through Notification Services we can send sms through sql server as well as recieve.

Can anyone provide me with links for the same.

Thanks
cmrhema

View 1 Replies View Related

SQL Server Notification Services : Help

Jul 20, 2005

Hi,I am a newbee to sql server. I know there is a tool calledNotification Services. I want to set up notification for specificentries in sql server table.Thanks a lot for your guidance.AJ

View 1 Replies View Related

Notification Services Failed To Open A Connection To SQL Server.Server Name:ABC(Microsoft.SqlServer.NotificationServices)

Jan 10, 2007



I am getting the above error when I try to create Notificatioin services new Instance.

Additional Information is

Failed to connect to server ABC(Microsoft.SqlServer.ConnectionInfo)

An error occured while establishing connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact that under default settings SQL Server deoes not allow remote connections.(Provider : Named Pipes Provider, error 40 -could not open connection to SQL Server)(Microsoft SQL Server error: 53)

Please help me I am new to SQL Server 2005.I can't able to proceed.

















View 6 Replies View Related

SQL Server Hardware Config (cpu Qty Vs Cpu Cache Vs Ram)

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

Integration Of SQL Server With Cache DB, Informix

Mar 12, 2008

we r doing some application and SQL server as backend DB Server. this sql server has to be updated continiously 24*7 with data from 3 external sources Cache DB, Iformix and Oracle DB servers. How to handle this in such away that all changes at 3 external sources to be updated to SQL server and this data will use by our application at frondend.
we though of ODBC., but ODBC is very very slow. It should lke Shadowing.
How to handle this?. any help will greatly appreciated.

View 2 Replies View Related

SQL Server Event Email Notification??

Aug 10, 2004

I am trying to set up the email notification system in SQL server 2000, but im having some problems. Mainly, im having problems with setting up an email account in the first place (there is a dedicated mail box, but the one im working with is a different box altogether).

If anyone could help fathom this out from start to finish i would be really grateful.

View 1 Replies View Related

Microsoft SQL Server Notification Services

Jan 13, 2004

Has anybody used it . can you give me an idea about the performance etc.?

View 6 Replies View Related

SQL Server 2008 :: Notification When Table Name Changes?

Jun 23, 2015

I have a situation where a person, who created our DW, likes to go in and change table name. This person had no prior SQL xp and is at a higher level then me in the company. I need to know whenever somebody changes a table name I get notified, with the old table name and the new table name. This can be either set on the server level or the DB level. I would like to use the policy management but cant find the way to do it.

View 9 Replies View Related

Notification From SQL Server To OLEDB Application

Mar 22, 2007

Hi

I am adapting an OLEDB-based application [ported on VC2005] to access a SQL Server 2005 Express database.

The application opens recordsets on tables and stored procedures. From this I can read records and update the database.



But how can I monitor the changes on this database made by other applications ?

Do triggers resolve the pb ?

View 3 Replies View Related

Can I Use SQL_Mobile As PC Desktop Server Or PC Local Cache Server?

Jun 14, 2006

Can I use SQL_Mobile as PC desktop server or PC local cache server?

I want many data to be cached at user's PC,
and call for this data to be created transparently as for remote
main SQL server.

Is it possible to do without creating complex structure of doubling functionality
of main queries to single SQL server ?

some articles, samples, links, keywords ?

thanks you

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

Integration Of SQL Server With Cache DB, Informix And Oracle

Mar 12, 2008

SUB: pushing data to SQL server from Informix, Oracle and Cache DB


we r doing some application and SQL server as backend DB Server. this sql server has to be updated continiously 24*7 with data from 3 external sources Cache DB, Iformix and Oracle DB servers. How to handle this in such away that all changes at 3 external sources to be updated to SQL server and this data will use by our application at frondend.
we though of ODBC., but ODBC is very very slow. It should lke Shadowing.
How to handle this?. any help will greatly appreciated.

Thanks in advance.

Prasad
vsrprasad16@gmail.com

View 1 Replies View Related

SQL Server 2008 :: View Log Cache Size

Jun 21, 2010

Can I view the log cache size in SQL Server memory any DMV's which states that.

View 9 Replies View Related







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