I am now using SqlDepency objects in a WCF windows service. This service could very well be running for weeks or even months at a time, in a perfect world...
I have some shared, global data caches that I now update only when the table's data changes, thanks to the SqlDepedency objects and Service Broker. I only have one question - what kind of considerations must I make when using this inside a long-running windows service? What if my SQL Server crashes, or the server is stopped and restarted, or someone trips on the cord... Will everything automatically work just as it should, or must I call SqlDepedency.Start() again, or possibly re-load my dataset and re-wire my OnChanged event to my SqlDepdency object? Is there any special events fired to notify me that I must do something of the sort? (e.g. maybe OnChanged will fire with some details). Or does Service Broker automatically handle all of this behind the scenes? Something tells me life isn't that easy...
Hello, I have a Question/doubt on SqlDependency.Start/SqlDependency.Stop.
After the implementation of my solution the question sounds "stupid" ... maybe is only the stress due to the fact that I'm going to deploy the application on Test.
Anyway.. I developed this application (windows application) that uses query Notification features to subscribe and get notification from Sql Server 2005 so it use the tipical pattern: SqlDependency.Start, SqlDependency_OnChange,SqlDependency.Stop.
This is application is installed on several client so actually I get have several notifications running on the Server.
When one of the client exit the application and call SqlDependency.Stop the service, queue and procedure are dropped.
Does the command drops all the query notification' service, queue and procedure running on the instance or only the one created by the specific user?
there was a post some time ago but nobody answered. Maybe somebosy will answer now?
I have created a class which contains a few UDF's. The class has a static constructor that reads from the databse and loads a Dictionary<> collection.
What is the lifetime of the class? What will cause the static constructor to be called again? When will it be garbage collected? If the life time is limited by default, can it be extended?
I have a conversation that I want to know has not ended so I am using LIFETIME.
When the conversation times out I then have three records in the queue.
1. The original conversation record that has not been received.
2. Error message to the initiator.
3. Error message to the target.
Both message bodies on the error records say that it was a lifetime error.
If I end the conversation on the initiator side after it is sent, I still get the target error record but the message_body field is null.
So say I don't end the conversation on the initator side. My next receive on the target side will pull the original record. Then it will pull the initator record and then it will pull the target record. Nothing on that record says that it had timed out.
What is the best practice for handling lifetime errors?
Given that the conversation states are as follows: (Thanks Rushi!)
Event Initiator Endpoint state Target Endpoint state
BEGIN DIALOG SO -- from Initiator to Target
SEND message(s) CO -- from Initiator to Target
Target receives a fragment CO SI of the first message sent or receives out of order message
Target received entire CO CO first message
END conversation at CO DO target
Initiator receives EndDialog DI DO message from target
Target receives ACK for the DI CD EndDialog message sent
END conversation at CD CD Initiator
When does the 30 minute timer start for clearing the conversation from the sys.conversation_handles table? Is it the same for both sides (initiator and Target) ie, the end conversation at the Initiator. I guess it must be just in case a resend is necessary.
I'm trying to get the HelloWorld_CLR project that comes with the SQL 2005 samples download to work, which it seems to. However, after I run the application a few times, an exception indicating "the service queue ClientQueue is disabled." After running the following query I notice the body of the error message is consistently "The dialog has exceeded the specified LIFETIME."
select service_name as [SERVICE],
message_type_name as [TYPE],
CAST(message_body as nvarchar(max)) as BODY
from ClientQueue
I believe that the problem is a poison message causing a RECEIVE statement to get rolled back 5 times, disabling the queue. My question is: How do I troublshoot the issue from this point?
I need to follow up on a message and check on its status. I am planning on using Conversation Timers (self addressed). I've tried it and they do work well. I am wondering if the LIFETIME parameter can be used for the same purpose. If the dialog has not been closed and the LIFETIME expires, will a message be queued into the service's queue? It does not seem that this is the case, but it is worth checking, as it could be a much desired feature.
We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:
1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.
2. Detach the database.
3. Re-attach the database.
4. Re-enable the queue
Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".
Has anyone else experienced this or can anyone suggest what we are doing wrong.
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX) DECLARE @Handle AS INT DECLARE @TransactionCount AS INT SELECT @Config = ' <ConfigurationDirectory > <ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
Hi, I've read about the subject a little bit and they are talking abut caching; eventhough it's good for some reason for my purpose not needed. So I tred such code which doens't work:): (I also enabled service broker on sql2005) Publicsqldep As New SqlDependency Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDependency.Start("Data Source=VPS05-101SQLEXPRESS;Initial Catalog=EM;Trusted_Connection=Yes") End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) If sqldep.HasChanges = True Then MsgBox("data changed") End If End Sub well what I'm doing is a very simple communication window on my web page where my users can post messages. There is a aimple chat table in my DB that store basicly the time and subject posted. And instead of making useless postbacks I want to refresh the window when a data entered into that chat table... Is the code above related to that purpose? Thanks
Hi all, I am using sqldependency in my web project.Is it possible to use it in my web project.the code is below. Here the dependency.onchange event is fired when i am changing somthing in database,otherwise the event will not fired so it is working fine.My problem is that when the event " private void dependency_OnChange(object sender, SqlNotificationEventArgs e) { try { RefreshData(); } catch (Exception) { } SqlDependency dependency = (SqlDependency)sender; dependency.OnChange -= dependency_OnChange; }" is fired so page get automatically give that changes in gridview.So what should be write after refereshdata() function get called and page should be refreshed there.please help me to solve my problem. Note: This code is working fine in window based application.So it will automatically display the changed data in gridview so how to display it in grid without refreshing the page.??????? using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient; public partial class Default2 : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { EnoughPermission(); connstr = "Data Source=NIHONGOW2003;Initial Catalog=DataWatcher;User ID=testlogin;pwd=testlogin"; string ssql = "select Id,Name from dbo.tbl_P "; SqlDependency.Stop(connstr); SqlDependency.Start(connstr); if (connection == null) connection = new SqlConnection(connstr); if (command == null) command = new SqlCommand(ssql, connection); if (myDataSet == null) myDataSet = new DataSet(); GetAdvtData(); } private DataSet myDataSet = null; private SqlConnection connection = null; private SqlCommand command = null; private string connstr; private bool EnoughPermission() { SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted); try { perm.Demand(); return true; } catch (System.Exception) { return false; } } SqlDependency dependency; private void GetAdvtData() { myDataSet.Clear(); command.Notification = null; dependency = new SqlDependency(command); Label1.Text = System.DateTime.Now.ToString(); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { adapter.Fill(myDataSet, "dbo.tbl_P"); GridView1.DataSource = myDataSet; GridView1.DataMember = "dbo.tbl_P"; GridView1.DataBind(); } } private void dependency_OnChange(object sender, SqlNotificationEventArgs e) { try { RefreshData(); } catch (Exception) { } SqlDependency dependency = (SqlDependency)sender; dependency.OnChange -= dependency_OnChange; }
private void RefreshData() { Label1.Text = "Database had some changes and are applied in the Grid"; GetAdvtData(); } }
I've been digging through this forum for an hour or two, and not found anyone having the same problem we are having.
Basically, we have a Windows service that uses sqldependency to get a notification on the existence of records to be processed. When the service receives the notification, it spawns multiple threads which each work on one of the records in the table, moving the records to a "completed" table. When the table is empty, the service sets up the sqldependency again and waits to do the whole thing over.
Our problem is, occasionally it stops being triggered. We don't know if the service stops listening, or the notification stops being sent, or if there is some other situation where the network connection between the db and the service is severed and the pieces can't resume their conversation.
In order to "fix" the problem, we restart the service. It does it's normal run of cleaning out the table, then sets the notification up again and works for another few hours before it flakes out again.
At the moment, we can't figure out how to determine whether it is the app, or the database, or network (or some combination of any of these parts) that is failing. If I could get some guidance as to how to start narrowing it down, I would be very grateful!
Let me know if you need more specific info, and thanks for any help you guys can offer!
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
I am new to Service Broker and Query Notification. I've used Books Online and the other help files to successfully create a class to subscribe and listen for mods to a table on a SqlServer 2005 (SP1 applied) server. My prototype worked exactly as expected.
However, when I put my code into production, the listener was calling my NotifyEvent() callback repeatedly, and without any changes being made to the table! Of course, I'd tested with a simple two column table and implemented with a more complex multi-column one. After some experimentation I discovered that the problem occured when the SELECT statement refrenced a "blob" column.
That is, the CREATE TABLE command included a column:
SqlServer called my NotifyEvent() continuously. If I removed the [Data] column from the select, then the code worked as I had tested. I also tried this on another table (fewer columns) that also had a [Data] defined similarly, and again the continuous notifications came. I've got a workaround because I also calculate a hash on the [Data] column, and I can "watch" the hash instead of the underlying [Data].
Oh, when I ask SSMS to script the table with the [Data] column it gives me:
I posted this in the .Net data access forum with no replies, so I'm trying it here and apologize for the cross-post.
I'm researching using SQLCacheDependency in an application to keep caches up to date between applications. I could also use SQLDependency, just haven't tried that yet.
I've tried a test where I read 1000 rows from a database, cache each one, and create the cache dependency. The dependencies work fine.
My problem is that it is 100 times slower to read and create a dependency than it is to just read the row.
At this rate, I need a 99% cache hit ratio just to make my caches break even! Is caching even worth it at this rate? Why is it so slow? Thanks very much for any insight.
Here is the code:
for (int i = 0; i < 1000; ++i) { SqlCommand oRowCommand = new SqlCommand( "[dbo].[usp_tblDoctor_Select]", oConnection); oRowCommand.CommandType = CommandType.StoredProcedure; oRowCommand.Parameters.Add(new SqlParameter("@id",i)); SqlDataReader objReader = null; SqlDependency oDependency = new SqlDependency(oRowCommand); oDependency.OnChange += new OnChangeEventHandler( OnNotificationChange); objReader = oRowCommand.ExecuteReader(); objReader.Close(); }
And the stored procedure that it uses for the dependency:
CREATE procedure [dbo].[usp_tblDoctor_Select] @id int as select fullname from [dbo].[tblDoctor] where [dbo].[tblDoctor].doctorid = @id GO
I also tried to end old conversation handles which appear to never get cleaned up. I was able to clean them up but this did not fix the problem either.
Here's a snapshot of the SQL profile showing reading of one row and the creation of the associated dependency:
Hello i'm a beginner with SSB. I wandering how can i retreive data when change occured on a specific table. At this time i'm able to receive a notification when data has changed on my table but if i would like to check data change on several table how to make it work??
I think meta data can be accessible in xml format but i have to clear idea on how retreive changed row information and table name from meta data.
We are currently in the process of testing a smart client application that makes use of SqlDependency to monitor and cache relevant data for requesting clients.
As part of our load testing we increased the number of clients requesting data. As we did this the server side process threw an exception and stated that a TemplateLimit had been reached.
TemplateLimit is described as "The subscribing query causes the number of templates on one of the target tables to exceed the maximum allowable limit." from the msdn documentation about the SqlNotificationInfo enumeration.
Is the TemplateLimit configurable and if not, what is the maximum allowable limit?
Hope someone can enlighten me on the limitations of query notifications.
I have a Client-Server - App where every Client-User has his own DB. The server is monitoring changes to all Client-DB's via SqlDependency. My problem can be reproduced with a small application, it even might be a €œfeature€? and not a €œbug€?:
- Consider two Databases TestDb1 and TestDb2 running on one SQL Server 2005 instance.
- Both DB€™s have identical Schemas.
- Consider the two DB€™s have each one table named €œTable1€?.
- Both tables have the same schema as already mentioned (the fields Id and Text).
- Now I setup a SQLDependency object on each Database:
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
}
If I make any changes to the Table in TestDb1 I get two notifications with the different Id€™s but the same Info,Source,Type (saying e.g. Data,Change,Update). If I make changes to the Table in TestDb2 I again get two notifications with the same result. As soon as I rename the Table in one of the Db€™s (e.g. Table2) and also change my Sql-Query in the code €“ I get just one Notification as expected. This behaviour is the same even If I change the connectionstring so that it points to another machine. So it somehow seems to fire a notification for every change to a table with the same name €“ regardless of the connectionstring where the physical change was done.
Does anybody know if this is a wanted behaviour of SqlDependency ? Does anybody know how I can set this up so I can have two DB€™s with identical Schemas and only get a Notification from the DB I actually changed ?
I am developing with .NET 2.0 and SQL Sever 2005 on WinXP for a production system of Windows 2003 Server with FIPS enabled (http://www.itl.nist.gov/fipspubs/).
When our team tries to use SqlDependency, we get FIPS exceptions. I assume that this is because the inner workings use encryption algorithms that are not FIPS-compliant. Is there a way to configure my application (or even the entire machine) such that SqlDependency will use FIPS-approved encryption?
I guess my real question is how can I use SqlDependency on a FIPS-enabled machine?
We have been using SqlNotificationRequest, but I'm now developing a client, of which, we require multiple instances. So we've run into the problem where when multiple clients run, only one will receive and process a notification.
I'm setting up an SqlDependency and things are starting to come together, but the following SELECT query results in a callback with type = Invalid and source = statement. However, I don't understand why that should be, as the select works fine "standalone":
mWatchQuery = New SqlClient.SqlCommand( "Select [pkLogId], [fkWho], [When], [WorkId], [What], [fkEventId] From dbo.tblLog", mDatabase.Connection)
mDependency = New SqlClient.SqlDependency(mWatchQuery)
I tried changing it using a table name alias L and L.[fkWho], L.[When] etc, but that fails too.
The property mDatabase.Connection returns the SqlConnection object for the object's database connection. Note I'm using the same connection every time - problem?
The tblLog DDL is as follows. Is it the use of a Text field?
To preface, I am trying to get a notification in a client side app from SQL Server 2005 (Express) when a datatable changes. The problem is I am getting these exceptions and I have no idea what is causing them, if it is a serious problem, or how to debug it from here.
Here is the setup:
I am working with SqlDependency in .NET 2.0 and running a simple app that just calls start and stop on the dependency object. The result is 3 exceptions in System.Data.Dll
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: A severe error occurred on the current command. The results, if any, should be discarded.'
Operation cancelled by user.
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: A severe error occurred on the current command. The results, if any, should be discarded.
Operation cancelled by user.
To duplicate this problem, do the following:
1) Create a database on SQL Server 2005 Express 2) Make sure the database has "ENABLE_BROKER" set. (ALTER DATABASE <DataBase> SET ENABLE_BROKER).
3) Turn on CLR exceptions in your debugger (debug->Exceptions->Common Language Runtime Exceptions)
4) Create a C# windows app. Add the following in form1_load.
I have a windows application in C# that pretty much is a copy of the example found in http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx although I am using a different database.
My problem is that the SqlDependency event OnChange is firing all the time although on insert, update or delete is performed in the database. The event fires approximately 1000times in one minute!
Hi, On my ASP.NET 2.0 application and SQL 2005 database server, i am using SQLDependency API to recieve notifications on the dataset changes from SQL 2005 server. I get the notification sucessfully (irrelevant of number of attempts). So here's the problem, i wait for 3-4 mins and make sure that there is no activity on my web server. Now if the data changes the service broker fires the event (i validated through trace). But the notification is never received by my web application. It gets lost in between. I read about this 'Abrupt client disconnects' problem in the article http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx but this didn't help much. Also i sometimes see following messages in the profiler <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>broker error intercepted</qnev:EventText><qnev:SubscriptionID>0</qnev:SubscriptionID><qnev:NotificationMsg><?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error></qnev:NotificationMsg><qnev:BrokerDlg>9EF36F45-E00D-DB11-85AB-0003FF0B72DB</qnev:BrokerDlg></qnev:QNEvent>
and also
<qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>broker error intercepted</qnev:EventText><qnev:SubscriptionID>0</qnev:SubscriptionID><qnev:NotificationMsg><?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-c0aac8a6-24a5-4a34-9d0f-0975538694c4&apos; because it does not exist.</Description></Error></qnev:NotificationMsg><qnev:BrokerDlg>4A639911-ED0D-DB11-85AB-0003FF0B72DB</qnev:BrokerDlg></qnev:QNEvent>
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) ?
"FROM dbo.request_queue INNER JOIN track on request_queue.track_id=track.track_id " +
"inner join artist on track.artist_id=artist.artist_id " +
"inner join album on track.album_id=album.album_id";
cmd.CommandType = CommandType.Text;
if (con.State != ConnectionState.Open)
con.Open();
dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
SqlDataReader rdr = cmd.ExecuteReader();
List<Track> l = new List<Track>();
while (rdr.Read())
{
Track t = new Track();
t.TrackID = (int)rdr["track_id"];
t.Filename = (string)rdr["file_name"];
if (rdr["track_name"] != DBNull.Value)
t.TrackName = (string)rdr["track_name"];
t.TrackNumber = (int)rdr["track_number"];
l.Add(t);
}
rdr.Close();
and for some reason, after i do multiple changes to the request_queue table, (adding rows), the dep_on_change never fires, and if i check dep.HasChanges it is always false.
Some info about my setup (all on the same local network):
Sql Server 2005 Standard Edition running on a Windows Server 2003 Standard Edition R2 development server VS Studio .NET Standard Edition running on my XP Pro x64 workstation.
I am working with the source code for transact-sql and console application that can be found here: http://www.codeproject.com/useritems/SqlDependencyPermissions.asp
This example works perfectly.
I am now trying to get SQLDependency working with a stored procedure. I create a sp via Studio Manager on my workstation with the following transact-sql:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE sp_test
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT ID, Name From Users
END
GO
I then add execute permissions for the subscribeUser and startUser users.
I change the code in the console application like so:
SqlCommand oCommand = new SqlCommand("dbo.getUsers", oConnection); oCommand.CommandType = CommandType.StoredProcedure;
When I run this application with this stored procedure rather than the "inline" SQL, the dependency.onchange event keeps firing and I get the following ouput:
Invalid: Subscribe
Any help or a solution will be greatly appreciated!
When starting an ASP.NET 2.0 I get the following error:
The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications.
Which happens on this line in my Global.asax [the penultimate line in Application_Start()]
SqlDependency.Start(css.ConnectionString);
Relevant web.config entries are:
<connectionStrings> <add name="MyDBConnString_localdev" connectionString="Data Source=.SQLEXPRESS;Initial Catalog=MyDB_40;Integrated Security=True;Pooling=True;Min Pool Size=5;Max Pool Size=80;Connection Lifetime=300;" providerName="System.Data.SqlClient"/> </connectionStrings>
The database is running on my local machine and was created from a backup copy of the production database.
q1: How can I troubleshoot this?
I tried each of the following, but had to cancel them as nothing much had happened after 3+ minutes (apart from the activity indicator 'Executing query' showing a green rotation in SQL Server Management Studio Express.) ALTER database MyDB set NEW_BROKER ALTER database MyDB SET ENABLE_BROKER
q2: Why does it take so long to run either of the above commands - without actually getting an error?
q3: What Service Broker configuration do I need for my database?
I created a SqlDependency object that uses a SqlCommand object that selects a single column:
Code Snippet SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select OrderID from Orders"; // Only one column! cmd.CommandType = CommandType.Text;
SqlDependency d = new SqlDependency( cmd ); d.OnChange += new OnChangeEventHandler(dependency_OnChange);
Now when I change the value of any field in table Orders, my query notification handler fires. Can I change this behaviour such that SqlDependency handler fires only if the specified column (OrderID) is changed?
I am trying to issue SQLDependency.Start() however I get the following error message.
{"The SELECT permission was denied on the object 'databases', database 'mssqlsystemresource',
schema 'sys'."}
If I change the permission of the logged-on account (the account is using SQL Server Authentication) to sysadmin, the statement runs successfully (I have tried lower permissions but none of these were successful). If I look in the Profiler it is the following call that fails :-
select is_broker_enabled from sys.databases where database_id=db_id()
Note that the broker is enabled - it is the read of whether the broker is enabled that is failing.
Note also that I issue a SQLDependency.Stop() just before this and it (the SQLDependency.Stop()) is successful.
Do I need to :-
- impersonate a sysadmin account when the SQLDependency.Start() is issued
- make my user sysadmin (not desireable)
- is there another (lower) permission (user or object) I can grant that allows the SQLDependency.Start() to be successful (best solution)
I have a WCF Windows Service which caches some query results. I am using the SqlDependency class to watch for any data changes in the backend SQL table, and then if so, update the loca caches.
Everything works great, except under concurrent use. If a client of the service runs a job, and at the same exact time, a user using the web application front end updates one of the SQL lookup tables, I get the following SqlClientException from the WCF Service: {"Transaction (Process ID 72) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."}. I could be wrong on the scenario that causes it, however I believe this is the case.
I'm not sure why this is happening to be honest.. however I have seen this issue in the past and we have solved it by using the 'WITH(NOLOCK)' clause on the query that is causing the error...
I tried simply adding WITH(NOLOCK) to the query that is being watched from inside the WCF Service - however then in my OnChanged event I got errors about the query not being correct. I have gone over the dtails on what invalidates a query for Service Broker (http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx) and I do not see NOLOCK on the list...
Is there something obvious I'm missing here? More importantly, since it appears this is invalid... what should I do to handle this situation? Can I just handle the SqlException on the client side and then re-try loading the dataset and hooking to OnChanged??
