Setting Up Query Notification And Performance Issue
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.
within sql server management studio... we have set up a few sql scripts to run at 2am every night (sql server agent > under jobs)... although it has been successful each night, we'd like to setup sql server agent to automatically email success and failure notifications to our email boxes.
i see under the properties of sql server agent > jobs > properties of a job > there is section that says Notifications but then it doesnt really have a place to type in which email we want to send them to? some reading material or instructions greatly appreciated
Hello Guys, I have a Question. I want to set up a sql alert that will monitor a particular counter(Eg. Memory:pages/sec) and send me an email when it reaches a particular threshold. My question is if i set up this, Will sql will start running a perfmon on the background and degrade server's performance OR Will it just read it from some system file or something to to get the values.
I dont know if the perfmon counter values are stored in any system files or not.
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?
We currently have a fairly new SQL server 2000 db (currently about 18mb is size) as a backend to an application (Navision). Performance seems to be below what it should be.
The db is increasing quite rapidly in size, with a lot of data scheduled to be loaded onto the db and also more and more shops and users coming onto the system with alot more transactions going onto the db.
The initial setup of the db has the database File properties set to "Automatically grow file" by "30%" and has an unrestricted file growth.
The server that the db sits on is high spec and very large disk space.
Because the database will be expanding alot and thus reaching its maximum space allocation and then performing a 30% increase in size (which I guess affects performance quite a bit??) quite regularly.
Is it best to set the intitial size of the db to a alot bigger size in the first place as we have large disk space availiable and also set the % increase bigger also.
any advice on best performance would be much appreicated.
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
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
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?
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€¦
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.
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'
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?
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
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.'
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?
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.
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
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
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.
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>'.
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.
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))
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.
I am trying to filter rows in a table using a query. It needs to be based on two parameters in separate columns. For example, I need to find people of a specific age group, and then further filter them by a hair color. How can I do this?
SQL Server 2005 is timing out when running loooong queries.Is there a global place I can extend the timeout for queries? I do not mean in code but just in general use?Thanks
i am using sqlserver 2000, and i was wondering how do i go aboutsetting the query time out. is there a way to configure the querytimeout for a specific user id?
I'm looking for a query I can use to alter table relationships. What I want to do in particular, is to set every relationship to cascade on update. Can anyone point me out to a solution? MSDN seems very vague in this subject.
I have a query in which I'd like to use the username of the user currently logged in. The expression im using to retrieve the username is: Membership.GetUser().UserName. Currently I have the following: <asp:SqlDataSource ID="ProjectSource" runat="server" ConnectionString="<%$ ConnectionStrings:Code %>" ProviderName="<%$ ConnectionStrings:Code.ProviderName %>" SelectCommand="Select Name, Namespace from Project where User = $Username"> <SelectParameters> <asp:Parameter DefaultValue="" Name="$Username" /> </SelectParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" DataSourceID="ProjectSource" />I'd somehow like to set up $Username to be equivlent to Membership.GetUser().UserName.
I've set the Query time-out parameter for my default MSDE instance to 1 sec (through the Enterprise Manager) and since the restart of the server I can't connect it through any client tool. Does anybody know how to return the default value of Query time-out (600s) without using enterprise manager?
Is it possible to set a query result (scalar) to scalar variable. I would like to set a qery result (SELECT COUNT(*) FROM MyTable) to a scalar variable:
DECLARE @temp int
SET @temp = query result...
Is it possible? I couldn't find the way to do that...
