Query Notification Not Fire All Subscriptions...
Jan 22, 2007
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
View 4 Replies
ADVERTISEMENT
May 16, 2007
Greetings,
We periodically get the following message in our three server peer-to-peer transactional replication environment:
"One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."
What causes a subscription to be marked inactive? What properties or settings can be adjusted to minimize or eliminate this occurrence?
Everthing seems to be running OK and then we get the message. It afflicts the higest change volume publication on our slowest overseas server.
Thanks,
BCB
View 9 Replies
View Related
Jan 18, 2007
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
View 1 Replies
View Related
Oct 30, 2007
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.
View 4 Replies
View Related
Jul 17, 2007
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.
View 3 Replies
View Related
Oct 26, 2007
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;
}
}
}
}
}
View 2 Replies
View Related
May 6, 2007
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
View 4 Replies
View Related
Jul 17, 2007
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
View 5 Replies
View Related
Jul 16, 2007
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
View 1 Replies
View Related
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
Feb 11, 2007
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
View 1 Replies
View Related
Oct 11, 2007
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.'
View 2 Replies
View Related
Dec 29, 2006
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.
View 7 Replies
View Related
Feb 8, 2007
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
View 7 Replies
View Related
Mar 27, 2008
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
View 3 Replies
View Related
Sep 13, 2006
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?
View 5 Replies
View Related
Feb 7, 2008
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
View 5 Replies
View Related
Dec 13, 2007
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.
View 1 Replies
View Related
Sep 1, 2006
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?
View 7 Replies
View Related
Dec 18, 2007
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!
View 2 Replies
View Related
Apr 13, 2006
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.
View 6 Replies
View Related
Jul 17, 2007
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
View 3 Replies
View Related
Mar 12, 2007
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
View 7 Replies
View Related
Aug 14, 2006
Hi Folks I hope every thing good.I have this problem I have a master page and an aspx page In aspx page there is an SqlDataSource Componant that call a storage prcedure from db that insert value to a certain table The issue here that he call the sp twice so the row record twince in the database What is the problem ?Thanks Bye
View 4 Replies
View Related
May 15, 2008
Hi
I have a some confusion pleaz resolve it
can i fire truncate to the multiple tables if you have any type of syntax then tell me and if not then could u resolve from store procedure
Ashish
View 1 Replies
View Related
Sep 21, 2005
Is it possible to create a trigger to monitor when a value changes from 1 to 0 in a particular row.
Example, rows have names, pins and status columns. When the status column of a row changes from 1 to 0, I am going to send a mail item to the person whose name has had the status changed.
Mike
View 1 Replies
View Related
Apr 16, 2007
Hello,
I have a SQL Server 2000 DTS package in which the first step executes a batch file. The batch file contains FTP commands that log into an FTP server, and pull down whatever file is there.
I set up a failure workflow to send an email if the step fails. When I have a SQL Server job run this package, and there is no file to dowload, the whole package fails without the failure workflow result firing.
For the step (DTSStep_DTSCreateProcessTask_1), I have the 'FailPackageOnError' property set to -1. In the package properties, I have the check box for 'Fail Package on First Error' cleared.
What do I need to do so that the failure workflow occurs when the step fails?
Thank you for your help!
cdun2
View 4 Replies
View Related
Sep 4, 2006
I am designing an application using service broker, and to increase performance of the application, sometimes I will be sending messages without waiting for a reply. After reading the article by Remus at http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx regarding fire and forget messages I realised I should always let the target end the conversation first so I can catch any errors. However, other times when i send a message from the initiator queue, I will want to wait for a reply.
If I have an activated stored procedure attached to the initator queue (as suggested in the article) would it mean that I would not be able to handly ANY replies manually (ie from the code that originally started the conversation).
If this is the case, should I have two initiating queues, one that handles fire and forget type requests and another where it waits for a reply, or should I just have some sort of clean up routine scheduled which checks for error replies and ends any other conversations that have been ended by the target?
View 1 Replies
View Related
Oct 20, 2006
After many problems with permissions I have got got SQL to accept a notification request but the public static void OnChange(object sender, SqlNotificationEventArgs e) is never triggered. The notification registers and de-registers ok. The same connect string successfuly connects to the same database to process queries.
I can see the GUID suffixed stored procedure, queue and service being created. Where does SQL2005 store the address/name of the routine it is to trigger? (When the notification is cancelled, the guid-siffixed items disappear) I have looked at the generated stored procedure, queue and service, but there is no indication of what is to be called back.
I have followed the instructions at http://msdn2.microsoft.com/en-us/library/ms181122.aspx, but so far without avail. I have checked the Application and system event logs, but there is indication therein. Also the SQL log.
So my questions are:
1) Where is the callback stored (is it a pointer or an actual name) ?
2) What steps should I take to resolve this?
View 7 Replies
View Related
Sep 18, 2006
Using vb.net, does anyone know or has anyone fired off a sql report, save it as PDF to a defined path?
I am knew to sql reporting services and the help files are not very helpfile.
Any sample code would be helpful.
Thanks
View 14 Replies
View Related
Jul 3, 2006
Hi there .NET community.
I was wondering if there was a way to launch a stored procedure froman aspx page without the sqlCommand object sitting waiting for the sproc to return something.The procedure takes about 10 minutes to execute and the page timeouts.Is there a way to just execute the procedure and procede to the next line of script?
Thanks.
CaptainCrabs
View 3 Replies
View Related
Mar 10, 2007
I am using SQL Server 2005 database for a webbased application built on .net 2.0. Here is my situation. I have a SubmitDate and a Status field in a table. I want the trigger to fire based on the datevalue in the SubmitDate field. SubmitDate field is the last date for the user to submit an application. For eg: if the SubmitDate value is 03/10/07 , I want the trigger to fire on :01 of 03/11/07 to change the Status field value to Inactive. Is that possible? Thoughts on how to do it?
View 1 Replies
View Related
May 2, 2007
Hi
In the page load of my webpage I call a databind for a gridview.
It generally calls this event handler :
protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
However sometimes (seemingly randomly) it doesn't.
Any ideas?
Thanks
p
protected void Page_Load(object sender, EventArgs e)
{
...
if (searchText != "")
SqlDataSource1.SelectParameters["search"].DefaultValue = searchText;
else{
SqlDataSource1.SelectParameters["search"].DefaultValue = "";
GridView1.DataBind();
}
View 2 Replies
View Related
