Dear,
I have designed a SiteMap provider for SQL Server. But I am worried about the notification service of SQL Server. My SiteMap requires service broker enabled in SQL Server. When I have web farm (multiple clustered server) does the Sql Server service broker notify all the web servers? Or, it notifies only the server request?
Have anubody implement such scenario? Looking forward for your reply.
Sincere Regards,
Sultan
Hay Gurus'
Please can you help out this with this question????
I have created a SQL 2005 select statement that returns an XML file with the correct items (in the correct format) to be used as a sitemap... This needs to have a function and then a select statement run in conjunction 1 CREATE FUNCTION QuickNav(@MenuItem int)
2 RETURNS XML
3 WITH RETURNS NULL ON NULL INPUT
4 BEGIN RETURN
5 (SELECT ID as "@ID", Title as "@title", url as "@url", Description as "@Description", ParentID as "@ParentID", NodeGroupParent as "@NodeGroupParent", ImageURL as "@ImageURL", NodeGroupSort as "@NodeGroupSort",
6 CASE WHEN NodeGroupParent=@MenuItem
7 THEN dbo.QuickNav(id)
8 END
9 FROM dbo.QuickNavView WHERE NodeGroupParent=@MenuItem
10 ORDER BY NodeGroupSort
11 FOR XML PATH('siteMapNode'), TYPE)
12 END 1 WITH XMLNAMESPACES (
2 DEFAULT 'http://schemas.microsoft.com/AspNet/SiteMap-File-1.0'
3 )
4 SELECT ID as "@ID", Title as "@title", url as "@url", Description as "@Description", ParentID as "@ParentID", NodeGroupParent as "@NodeGroupParent", ImageURL as "@ImageURL", NodeGroupSort as "@NodeGroupSort",
5 CASE WHEN id=31
6 THEN dbo.QuickNav(id)
7 END
8 FROM QuickNavView
9 WHERE id=31
10 ORDER BY NodeGroupSort
11 FOR XML PATH('siteMapNode'), ROOT('siteMap')
So when i run this code in SQL 2005 Express it works fine and i can save the results as a Web.sitemap and my menu looks ok...But now i want to automate this creation process.... have a button that when clicked saves an XML file (Web.sitemap) to a server directory... How Can I Do This????? This may seem like a dumb question but i really do need help and have searched everwhere... any help, pointers to a tutorial would be great...Any Ideas .....Tod BTW the results look like... (and work fine with both tree control and menu) 1 <siteMap xmlns="http://schemas.microsoft.com/AspNet/SiteMap-File-1.0">
2 <siteMapNode ID="31" title="root" url="~/Default.aspx" ImageURL="~imagesEASLogoSmall.jpg" NodeGroupSort="1">
3 <siteMapNode ID="34" title="Business" Description="Group node for Business Objects" NodeGroupParent="31" xmlns="">
4 <siteMapNode ID="1" title="Business Case" url="~/EASObjectInventory.aspx?LookUPID=BusinessCase" NodeGroupParent="34" ImageURL="~imagesBusinessCase.gif" NodeGroupSort="100" />
5 <siteMapNode ID="19" title="Business Plan" url="~/EASObjectInventory.aspx?LookUPID=BusinessPlan" NodeGroupParent="34" ImageURL="~imagesBusinessPlan.gif" NodeGroupSort="200">
6 <siteMapNode ID="9" title="Mission" url="~/EASObjectInventory.aspx?LookUPID=Mission" ParentID="19" NodeGroupParent="19" ImageURL="~imagesMission.gif" NodeGroupSort="210" />
7 <siteMapNode ID="10" title="Vision" url="~/EASObjectInventory.aspx?LookUPID=Vision" ParentID="9" NodeGroupParent="19" ImageURL="~imagesVision.gif" NodeGroupSort="220" />
8 <siteMapNode ID="11" title="Goal" url="~/EASObjectInventory.aspx?LookUPID=Goal" ParentID="10" NodeGroupParent="19" ImageURL="~imagesGoal.gif" NodeGroupSort="230" />
9 <siteMapNode ID="12" title="Strategic Objective" url="~/EASObjectInventory.aspx?LookUPID=Strategic Objective" ParentID="11" NodeGroupParent="19" ImageURL="~imagesObjective.gif" NodeGroupSort="240" />
10 </siteMapNode>
11 <siteMapNode ID="7" title="Bussiness Concept" url="~/EASObjectInventory.aspx?LookUPID=BusinessConcept" NodeGroupParent="34" ImageURL="~imagesBusinessConcept.gif" NodeGroupSort="300" />
12 </siteMapNode>
13 <siteMapNode ID="24" title="Compliance Item" url="~/EASObjectInventory.aspx?LookUPID=ComplianceItem" NodeGroupParent="31" ImageURL="~imagesComplianceItem.gif" NodeGroupSort="400" xmlns="" />
14 <siteMapNode ID="32" title="Exposure" url="~/EASObjectInventory.aspx?LookUPID=Exposure" NodeGroupParent="31" ImageURL="~imagesRisk.gif" NodeGroupSort="500" xmlns="" />
15 <siteMapNode ID="26" title="Human Resource" url="~/EASObjectInventory.aspx?LookUPID=HumanResource" NodeGroupParent="31" ImageURL="~imagesHumanResources.gif" NodeGroupSort="600" xmlns="" />
16 <siteMapNode ID="13" title="IT Component" url="~/EASObjectInventory.aspx?LookUPID=ITComponent" NodeGroupParent="31" ImageURL="~imagesITComponent.gif" NodeGroupSort="700" xmlns="" />
17 <siteMapNode ID="14" title="IT Hardware" url="~/EASObjectInventory.aspx?LookUPID=ITHardware" NodeGroupParent="31" ImageURL="~imagesITHardware.gif" NodeGroupSort="800" xmlns="" />
18 <siteMapNode ID="16" title="KPI" url="~/EASObjectInventory.aspx?LookUPID=KPI" NodeGroupParent="31" ImageURL="~imagesKPI.gif" NodeGroupSort="900" xmlns="" />
19 <siteMapNode ID="18" title="Portfolio" url="~/EASObjectInventory.aspx?LookUPID=Portfolio" NodeGroupParent="31" ImageURL="~imagesPortfolio.gif" NodeGroupSort="1000" xmlns="" />
20 <siteMapNode ID="27" title="Process" url="~/EASObjectInventory.aspx?LookUPID=Process" NodeGroupParent="31" ImageURL="~imagesProcess.gif" NodeGroupSort="1100" xmlns="" />
21 <siteMapNode ID="20" title="Product" url="~/EASObjectInventory.aspx?LookUPID=Product" NodeGroupParent="31" ImageURL="~imagesProduct.gif" NodeGroupSort="1200" xmlns="" />
22 <siteMapNode ID="29" title="Program" url="~/EASObjectInventory.aspx?LookUPID=Program" NodeGroupParent="31" ImageURL="~imagesProgram.gif" NodeGroupSort="1300" xmlns="" />
23 <siteMapNode ID="17" title="Project" url="~/EASObjectInventory.aspx?LookUPID=Project" NodeGroupParent="31" ImageURL="~imagesProject.gif" NodeGroupSort="1400" xmlns="" />
24 <siteMapNode ID="21" title="Service" url="~/EASObjectInventory.aspx?LookUPID=Service" NodeGroupParent="31" ImageURL="~imagesService.gif" NodeGroupSort="1600" xmlns="" />
25 <siteMapNode ID="25" title="Stakeholder" url="~/EASObjectInventory.aspx?LookUPID=Stakeholder" NodeGroupParent="31" ImageURL="~imagesStakeholder.gif" NodeGroupSort="1700" xmlns="" />
26 <siteMapNode ID="23" title="Standard" url="~/EASObjectInventory.aspx?LookUPID=Standard" NodeGroupParent="31" ImageURL="~imagesStandard.gif" NodeGroupSort="1800" xmlns="" />
27 <siteMapNode ID="35" title="Administration" url="~/admin/Default.aspx" NodeGroupParent="31" NodeGroupSort="5000" xmlns="" />
28 </siteMapNode>
29 </siteMap>
I am building myself a datadriven menu control and I have got one table where I store all menu items as rows. On each row there is a column for roles, in this column I have added one or several roles as a string. Today I can retrieve all menu items (rows) for a requested role, but how can I retrieve menu items for two or more roles? I have each role inserted as rows in a temp table (@Role), if that makes it easier?DECLARE @Role TABLE ( role varchar(15)) SELECT SiteMap.Id, SiteMap.Title, SiteMap.UrlFROM SiteMapWHERE (CHARINDEX(@Roles, SiteMap.Roles) > 0 ) Regards, Sigurd
View 2 Replies View Related
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (inventoryIds)
this scenario the inventoryids is a collection of ids like below
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (1,2,3,4,5)
this scenario i want to pass the ids at run time as a string in C#
Also how will i take the string in the DA class(C#) to execute it
guide me please
Hello All,
I have two columns in MS-SQL, 1st column has Web-Links and 2nd has Dates
Example:
Names
Date
Link1
Date1
Link2
Date2
Link3
Date3
Link4
Date4
Link5
Date5
Link6
Date6
Required Output:
<?xml version="1.0" encoding="UTF-8"?><urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> <loc>http://www.example.com/Link1</loc> <lastmod>2005-01-01</lastmod> <changefreq>monthly</changefreq> <priority>0.8</priority> </url>
etc.....
</urlset>
Thanks.
- David
hi,
When SQL server notifies the application that the records in a table has changed, is there a way to know which record has changed from the application?
Let me give an eg.
Consider that we have a table customer. We have registered for a notfication for 2 customers, say Cust id 1 and Cust id 2. Now , when records for these two customers changes we get a notification. Is there a way in .Net by whihc we can get whihc customer has been modified?
Thanks
Man.
Hi everyone I have a class called SqlSiteMapProvider which extends StaticSiteMapProvider to dynamically create the sitemap data for my site. Although I am getting a problem in that whenever I add a page to the database (via the application) it doesnt' seem to appear in the site map, even though the data is saving to the database .
If I edit the class, save and refresh the app, then the page does appear.
Can anyone think of why this might be? Or know how I can fix it. Is there some kind of application refresh that I need to do or something?
Thanks
Dave
Hi I have already posted this yesterday (http://forums.asp.net/p/1111248/1711653.aspx#1711653) but think it might have been lost by the wayside, can anyone help me?
I have a class called SqlSiteMapProvider which extends StaticSiteMapProvider to dynamically create the sitemap data for my site. Although I am getting a problem in that whenever I add a page to the database (via the application) it doesnt' seem to appear in the site map, even though the data is saving to the database .
If I edit the class, save and refresh the app, then the page does appear.
Can anyone think of why this might be? Or know how I can fix it. Is there some kind of application refresh that I need to do or something?
Thanks
Dave
I've got an application that's using query notifications with a two-user setup (a user that starts the dependency running, and an application user that subscribes to it). I'm having problems getting the query notifications to go through however... according to the Profiler, when the application user does something that triggers a query notification, it causes this error:
This message could not be delivered because the user with ID [application user] in database ID [database] does not have permission to send to the service. Service name: SqlQueryNotificationService-[guid].
The problem is that [guid] is different each time, and there doesn't seem to be a generic "grant send on all services" statement, so I can't grant send on the particular service in question to the application user to get rid of the error. (If I grant db_owner to the application user, the problem goes away and everything works, but I'd rather not do that. The application user already has subscribe query notifications, receive on QueryNotificationErrorsQueue, and references on contract::...PostQueryNotification.)
Obviously I'm doing something wrong, but I am completely out of ideas and can't seem to find anything new to try. Any ideas? Thanks.
After having problems with SqlDependency logging an infinite number of errors I've rewritten my code to use SqlNotificationRequest instead. Now I have a different kind of cleanup problem and am looking for help.
My code registers to be notified when table data is changed. When the event triggers, it re-registers. It works fine. The problem comes when my subscribing program ends: it orphans the last subscription. When the program re-starts it subscribes as before, but now there are now two subscriptions (or three, or however many times my program is shut down). As an Admin I can run KILL QUERY NOTIFICATION SUBSCRIPTION ALL; I can even hard code this into my app as it is the only one (for now) using this instance of SS. But there must be a better way.
When I subscribe (.NET) how do I get the subscription id? Then I could just KILL that single id when my code shuts down. Specifically what code should I include in StopListener()? Thank you.
public void StartListener()
{
// starts a new thread whose sole purpose is to sleep until waked
Debug.WriteLine( "RequestNotification: StartListener" );
if( null == _listener )
{
_listener = new Thread( Listen );
_listener.Name = _threadName;
_listener.IsBackground = true; // essential!
_listener.Start();
}
}
public void StopListener()
{
Debug.WriteLine( "RequestNotification: StopListener" );
// not necessary, but good practice
_listener.Abort();
_listener = null;
Debug.WriteLine( "RequestNotification: Listener aborted..." );
}
private void Listen()
{
// infinite loop on this thread: keep listening even after
// receiving notification
while( true )
{
// connects to SqlServer queue and sleeps until a notification msg arrives
using( SqlConnection conn = new SqlConnection( _connect ) )
{
conn.Open();
Debug.WriteLine( "RequestNotification: Listen connection opened..." );
using( SqlCommand cmd = conn.CreateCommand() )
{
cmd.CommandText = "WAITFOR ( RECEIVE * FROM " + _queue + ");";
cmd.CommandTimeout = 0; // _notificationTimeout + 100;
try
{
// the thread is blocked until the waitfor event occurs.
SqlDataReader reader = cmd.ExecuteReader();
// the waitfor has triggered
while( reader.Read() )
{
// messageText = System.Text.ASCIIEncoding.ASCII.GetString( (byte[]) reader.GetValue( 13 ) ).ToString();
}
// and now we need to hand things back to the user's passed
// delegate, ensure we're back on the UI thread as well
//object[] args = { this, EventArgs.Empty };
Debug.WriteLine( "RequestNotification: Notification received." );
SqlNotificationEventArgs e = new SqlNotificationEventArgs( SqlNotificationType.Change, SqlNotificationInfo.AlreadyChanged, SqlNotificationSource.Database );
_delegate( this, e );
}
catch( SqlException )
{
// if the queue name doesn't match one in the db an exception will be thrown
throw;
}
}
}
}
}
Hi All
In Work , we created a Cache component that serves a large business application, we are subscribing to almost 200 queries , the component worked quite well in the first few months of the development, but lately i am getting notification events that contain "Error" in the SqlNotificationEventArgs of the event , the "Source" value is "Client".
I have no idea what might cause this error event , we are close to "go live" with our product and we cannot depend on the Query Notification mechanism until this problem is solved.
Can it be a DataBase problem? Application Problem?
Thanks in advance
Eden
Hello all,
We have encountered a weird problem; we have an application that registers to several queries for notification.
For several weeks everything worked fine, any change to any query result in the database triggered the notification event, but since yesterday the mystery began€¦ since yesterday not all changes triggers events , changing one table raise the notification event, changing the other doesn€™t , all seems fine in the database , the application reports that all queries were registered successfully , in the subscription view in the database we can see that all the queries that the application subscribed to are present€¦
What can be the problem? Is it something in the database? Can it be a problem in our application, please keep in mind that everything worked until yesterday€¦
Thanks,
Eden
Hi all,
I am looking at replacing a polled system with Query Notification. However when I create the SqlDependency I need to be sure I have the correct permissions. I check the SqlClientPermissions via the Demand() method, but also want to ensure I have the correct DB permission given my current connection string. As I understand it I need to have the following permissions:
CREATE PROCEDURE, QUEUE, and SERVICE permissions
SUBSCRIBE QUERY NOTIFICATIONS
SELECT on underlying tables
RECEIVE on QueryNotificationErrorsQueue
I check most of these via the 'has_perms_by_name' function, but cannot find the correct syntax to check for RECEIVE on QueryNotificationErrorsQueue. I would also love to find a way to do this via SMO instead of issuing SQl commands. Also am I missing any checks ....
Finally, I have also run into the problem whereby SQL issues the following error:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-1fd90369-7781-4bad-a1b7-e1b56e328374] running on queue ImlHostDB.dbo.SqlQueryNotificationService-1fd90369-7781-4bad-a1b7-e1b56e328374 output the following: 'Could not obtain information about Windows NT group/user 'EMEADyerN', error code 0x54b.'
I ran into this issue when I bought my machine out of sleep mode with it no longer connected to the network. Is their no way to get a error notification. In this situation I will just stop seeing notification and without looing at the ErrorLog believe their is nothing wrong.
Many Thanks, Nick
Hi Guys,
I'm having some problems setting up a SQL Server 2005 Query Notification application. I've only recently moved from SQL Server 2000 so 2005 is still a little alien to me!
I've followed example found on the internet in the following steps:
1.
ALTER DATABASE <dbname> SET ENABLE_BROKER
2.
GRANT CREATE PROCEDURE TO <user>
GRANT CREATE QUEUE TO <user>
GRANT CREATE SERVICE TO <user>
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <user>
GRANT RECEIVE ON QueryNotificationErrorsQueue TO <user>
3.
ALTER DATABASE <dbname> SET TRUSTWORTHY ON
The application developers have also followed the same example setting up SQL cache and the connection code. When they start the application I can see the connection in the activity monitor but it has a status of SUSPENDED. I've also checked the view:
SELECT * FROM sys.dm_qn_subscriptions
There are no subscriptions setup I can however see that there is a new SP created. I tried to creating master keys as well but I'm not sure what the reasoning behind this is.
Is there anything else I need to take into consideration or check? When I change the data which they use for their select statement SQL profiler doesn't register anything (except the update). I have however seen this:
exec sp_executesql N'END CONVERSATION @p1; BEGIN CONVERSATION TIMER (''69e2c786-9d33-dc11-a751-0050568146e8'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name,
conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-5fb25d04-6ef6-47e6-9406-854f15e16eb7]), TIMEOUT @p2;',N'@p2 int,@p1
uniqueidentifier',@p2=60000,@p1='BD98F6B4-A133-DC11-A751-0050568146E8'
This occurs when they refresh the page.
Any ideas?
Cheers!
Ian
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
Two days ago I discovered that the drive on our test SqlServer2005 was full. The current Error_log had 54+ Gb (yes Gb) of records. Not only did I delete the file I restarted SS and the server.
That afternoon, I discovered that the current log had 33,678 records! I believe 33,616 of the records came from a single run of my app and set of program events. Obviously I'm doing something wrong in my Query Notification implementation and testing. And yet, when I try to to deliberately trigger the phenomenon, I can not.
Here is the head of the exported log file; the two msgs (query dialog closed and cannot drop queue) alternate thousands and thousands of times form a single test run. What is going on?! Pls help!
Date,Source,Severity,Message
10/11/2007 11:42:52,spid5s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
10/11/2007 11:42:52,spid5s,Unknown,SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
10/11/2007 11:42:49,spid14s,Unknown,Service Broker manager has shut down.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
10/11/2007 11:42:47,spid51,Unknown,Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
10/11/2007 11:42:04,spid51,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid52s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid54s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
10/11/2007 11:42:03,spid54s,Unknown,The query notification dialog on conversation handle '{72FF98A6-1478-DC11-B117-003048772A14}.' 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>'.
10/11/2007 11:42:03,spid52s,Unknown,The activated proc [ovs].[SqlQueryNotificationStoredProcedure-ed59bba2-19b1-42a3-9aaa-f8a76b844561] running on queue OVS_GOM_Protected.ovs.SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561 output the following: 'Cannot drop the queue 'SqlQueryNotificationService-ed59bba2-19b1-42a3-9aaa-f8a76b844561'<c/> because it does not exist or you do not have permission.'
Hi All
We built a Cache component that take advantage of the SQL Server 2005 query notification mechanism, all went well , we tested the component in a console application , and notifications kept coming for as long time as the console application ran.
When we initiate our Cache Component in our web service global.asx application start event , the query notification works for a few minutes , but if we came after 10 minutes or so , we stoped getting notifications from sql, the SQL Server queue is empty , and all is showing that there is nothing wrong on the DB side...
Our Cache component is a Singleton class , that perform all registrations ,catch the notification events and resubscribe for notifications.
What can be the problem? is our Cache component object are being collected by GC?
Does IIS disposes the SQL Connection that the Query notification uses?
We are on a crisis...
Thanks in advance.
We are in development stage of an application which uses Query Notification feature of SQL Server 2005. When the development started what the development team did was, they just Enabled broker on the database by SET ENABLE_Broker statement and they started using Query Notification. Now the application is in testing phase and when we test with many user, the performance is really slow. What i want to know is , when we use Query Notification what all are the setting to be done at the database level. Is this setting enough. And also how can i tune this system. any useful link is appreciated.
thanks in advance
Madhu
I'm using query notifications and it worked fine as long as I just edited the data that the "query points to" (one table). That is, my graphical represenation of the data (in a gridview) was updated correctly with the new data 1ms after I changed it, just like it should.
When I inserted a new row to the table the query notification stopped working, meaning my gridview wasn't updated. Any idea of why? Also, after this, not even editing of the data made the query notification trigger so it must have totally stopped.
Thanks in advance
ripern
The title says it all. You can do it from a ADO.Net client so why not from TSQL.
It all uses the service broker stuff so why not?
In order to avoid a large ammount of network traffic we decided to use Query Notification to Update our Clients.
Usually we have 20 up to 200 Clients connected to the DB, and some of the data QN is pointing at, are changed frequently (about 5 times per second) .
Now i heard it is recomended to connect just 10 Clients (max) to get acceptable performance out of SQL Server.
So my questions:
Is Query Notification the rigth technique to handle this kind of Data Changes.?
Is there an other feature or technique to get updated at client-side with less performance and ressource consumtion (without polling)?
Are there improvements in SQL-Server 2008?
How should the Server be configured to handle more than 10 Clients?
Thanks in advance
Raimund
I have noticed something strange on the select statement that you can use on query notification.
I have a table that contains a compute column let's call it "Calculate".
If I subscribe to query notification:
Select ID from table
the subscription fails.
The column Calculate doesn't use the column ID.
Why??
for me it shouldn't be the case
Thankx
MArina B.
I tried using Query Notification on my computer at home:
* Win XP Pro with all the SPs and hotfixes
* SQL 2005 with SP1 qand hotfix
Query Notification worked fine.
Then I tried using it at work:
* Win XP Pro with all the SPs and hotfixes
* SQL 2005 with SP1 qand hotfix
and I see the following error in the SQL server log file and notification does not get to the client app:
----------------------------------------------------------
Date 9/1/2006 10:18:30 AM
Log SQL Server (Current - 9/1/2006 10:18:00 AM)
Source spid17s
Message
An
exception occurred while enqueueing a message in the target queue.
Error: 15404, State: 19. Could not obtain information about Windows NT
group/user 'domainmyuser', error code 0x6e.
----------------------------------------------------------
A similar error shows up in the machine's Event Log.
I am
sysadmin and full OS admin on both boxes. The difference is that the
computer at home is standalone while the one at work is part of a
domain.
What could be wrong?
I found in error log of my server is full of message like this
The query notification dialog on conversation handle '{9AD14600-28AD-DC11-9B36-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-174a4df0-bac8-4a51-8564-28d750d7c11a'.</Description></Error>'.
any idea what could cause that?
thanks!
My database is logging frequent errors and I am unable to determine the cause. These errors appear to be related to the Service Broker. Below is the database log file after a database restart and attempted access to the database through a web application. The first error (bottom of the logfile) is error 28054. I have searched on this error code and have found nothing helpful. Any assistance or direction would be greatly appreciated.
Database Log:
04/13/2006 12:26:05,spid22s,Unknown,An error occurred in the service broker message dispatcher<c/> Error: 15517 State: 1.
04/13/2006 12:26:05,spid22s,Unknown,Error: 9644<c/> Severity: 16<c/> State: 14.
04/13/2006 12:26:04,spid57s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid22s,Unknown,An error occurred in the service broker message dispatcher<c/> Error: 15517 State: 1.
04/13/2006 12:26:01,spid22s,Unknown,Error: 9644<c/> Severity: 16<c/> State: 14.
04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid56s,Unknown,The activated proc [dbo].[SqlQueryNotificationStoredProcedure-aa148e0f-2980-4a23-b9cf-b44dbfddf783] running on queue CDR.dbo.SqlQueryNotificationService-aa148e0f-2980-4a23-b9cf-b44dbfddf783 output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.'
04/13/2006 12:26:01,spid52,Unknown,Service Broker needs to access the master key in the database 'CDR'. Error code:25. The master key has to exist and the service master key encryption is required.
04/13/2006 12:26:01,spid52,Unknown,Error: 28054<c/> Severity: 11<c/> State: 1.
Hi all,
I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:
I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.
This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:
private bool Start()
{
try
{
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop(_currentConnectionString);
if (SqlDependency.Start(_currentConnectionString))
{
if (connection == null)
{
connection = new SqlConnection(_currentConnectionString);
}
if (command == null)
{
command = new SqlCommand(_sqlQueryString, connection);
}
return (true);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
private void Stop()
{
SqlDependency.Stop(_currentConnectionString);
if (command != null)
{
command.Notification = null;
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
private void GetDataSnapshot()
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataToWatch, tableName);
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
++changeCount;
lblStatus.Text = String.Format(statusMessage, changeCount);
// Add information from the event arguments to the list box
// for debugging purposes only.
//lbChanges.Items.Clear();
lbChanges.Items.Add("+++++++++++++++++");
lbChanges.Items.Add("Id: " + dependency.Id);
lbChanges.Items.Add("Info: " + e.Info.ToString());
lbChanges.Items.Add("Source: " + e.Source.ToString());
lbChanges.Items.Add("Type: " + e.Type.ToString());
// Reload the dataset that is bound to the grid.
if (e.Info != SqlNotificationInfo.Error)
this.GetDataSnapshot();
}
Thanks, Nick
Hi everyone, Anyone know how SQLDependency works in a web farm setup? Which machine will receive the notification - All of them or just the machine that started the dependency?Marc
View 1 Replies View RelatedI am developing a web app that uses a SQL Server backend and will be served by a Web Farm at some point in the future. Can anyone give me some guidance at to what the common architectual considerations are when using SQL Server with a Web Farm? Initially, would it be allright to continue to have a single SQL Server on the network, with several webservers connecting to it instead of just one, or would this be a big performance nightmare? Eventually, since the goal of using the Web Farm would be to reduce individual server load, I would think having multiple SQL server instances would be preferred. Is this even done in the real world? I'm not a dba, so I'm not quite sure of the how multiple instances would be synchronized so that they held the same data and how applications on varying web servers would know to connect to one or another (by varying the connections string, perhaps). Can anyone give me any guidance? Thanks!
View 4 Replies View RelatedOur topology:
Internal corporate network domain utilizing Integrated Windows Authentication between local workstations and the web and database servers. (domain similiar to: http://reports.ourdomain.com)
Machine #1: Windows 2003 Server w/SQL Server 2005 (SP2 and hotfixes applied) running SSRS.
Machine #2: Windows 2003 Server w/SSRS only. (pointing to the SQL DB on Machine #1)
Machine #3: Windows 2003 Server w/SSRS only. (pointing to the SQL DB on Machine #1)
Device #1: Arrowpoint load balancer for Machines #2 & #3 (w/sticky sessions).
We recently added 2 web servers and configured them to load balance the web requests to a seperate SQL Server machine. (the load balancing is done thru an Arrowpoint device not Microsoft NLB)
The installation of reporting services (on Machine #2 & #3) went smoothly and the configuration was a breeze. But we could not access the "http://reports.ourdomain.com/Reports" directory for reporting services on the 2 web server machines. We received the "401.1 - Unauthorized" http error.
Since we were using a domain name to access the report server we made sure to update the <URL> tag in the config files.
File location 1:
C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerRSWebApplication.config
<ReportServerUrl>http://reports.ourdomain.com/ReportServer</ReportServerUrl>
File location 2:
C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServer
sreportserver.config
<UrlRoot>http://reports.ourdomain.com/reportserver</UrlRoot>
We still received the error, so then we added a couple of entries to the HOSTS file on each web server:
File location -->
C:WINDOWSsystem32driversetcHOSTS
In order for the load balancers to "see" themselves from the domain reference we had to update the HOSTS file
on each web server to have the domain name "reports.ourdomain.com" point to the IP Address of the specific web server.
(we also added the IP of Machine #1 just to be safe):
MACHINE #2's HOSTS file:
127.0.0.1 localhost
10.10.0.1 Machine1
10.10.0.2 reports.ourdomain.com #IP of Machine2
MACHINE #3's HOSTS file:
127.0.0.1 localhost
10.10.0.1 Machine1
10.10.0.3 reports.ourdomain.com #IP of Machine3
Then the web servers were able to authenticate the users using IWA and NTLM
and we could access the report servers normally. Even the the scheduled reports
were being load balanced and emailed from the web servers which we didn't
expect to happen. (pretty cool!)
I hope this helps someone else as it tooks us about 4 days to figure this out.
Server Software
SQL Server 2000 (all sps up to date)
SSRS 2000 (all sps up to date)
Scenario:
1 - SSRS + SQL2000 Database server
2 - Web servers
We have installed SSRS on 2 web servers that are acting as a web farm for a central Report Server.
After we installed SSRS on the 2 web servers the scheduled reports on the central Report Server stopped working.
We tried disabling the ReportServer service on the web servers and then the scheduled reports began to work again.
Why would the ReportServer service on the 2 web servers interfere with the central Report Server?
Can the web servers be used to load balance the scheduled reports?
Any advice would be appreciated.
I have to install several AG on a cluster for a whole SharePoint production farm, it's a nice to have (1 AG for Search database, 1 AG for Profil database, 1 AG for Content database, 1 for applicatif, etc...) We have 2 Front End load balanced, 1 application server and 3 SQL HADR: 2 nodes on synchrone (sql 2014 enterprise licence) and the 3rd asynchrone (standard licence).
The SharePoint farm is configured with the SQL Alias on the primary node, then if there is a failover on node 1, I must move the sql Alias to the second node manualy, unless I'd configured this Alias to point on the AG listener of the availability group in order to use the automatic failover. But as we have several AG, should I create one listener per each AG?
And which AG listener should I have to configure on the SQL Alias then ?
Hi,
SQL Server 2005 SP1. (+ 5 minutes fix installed).
Query notification subscription cretaed (sys.dm_qn_subscriptions).
Subscription does not fire in response to event.
sys.transmission_queue is empty.
profiler does not show errors.
sys.conversation_endpoints shows one endpoint in status SO. (along with a lot of left over garbage - about 300K records - but this should not be a problem other than storage).
I know SP2 is suppose to solve the garbage records problem but this is not our problem. This setup was working and then just stoped.
any ideas ?
Boaz
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.