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?
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?
I m trying to use the insert statement with in the function ! and i m getting this errror !
Server: Msg 443, Level 16, State 2, Procedure GetTotalCOst, Line 16 Invalid use of 'INSERT' within a function.
Please help me how to rectify it and how i can use the Insert statement with in the function !
Here is the code for the function.
create function dbo.GetTotalCOst(@varWork_no as numeric,@varSubWork_no as numeric)returns numeric as begin Declare @valCost integer Declare @TotService integer Declare @TotParts integer Declare @TotLabour integer Declare @TotTravel integer Declare @TotSubContract integer select @TotService= isnull(sum(quantity*costprice),0) From SB_Service_Suppply_Details where work_no=@varWork_no and subwork_no=@varSubWork_no select @TotParts= isnull(sum(quantity*costprice),0) From SB_PARTS_dETAILS where work_no=@varWork_no and subwork_no=@varSubWork_no insert into dbo.SB_InvoiceCostingService values(@TotService,@TotParts,1,1,1,1,1,1) return (@valCost) end
Here's my update statement:UPDATE Item1SET reviewloop = 1, currentreviewstate=5WHERE itemid in(SELECT itemid FROM Item2)The thing is: the table Item2 DOES NOT HAVE a field called itemid.So, I should receive an error, right? Not so.Instead, every singlerecord in Item1 was updated.Does anyone know why SQL Serverr does not trown an error???Thanks guys,-Silvio Souza
I have the following code, which creates an error. However, if I replacement the SQL statement with the one in comments, it works fine, so I feel like my connection is okay. It's pretty simple code, really. I'm not sure what could be wrong.
public DataTable GetNextConfession() {
DataTable dt = new DataTable(); dt.TableName = "XMLConfession";
// including this line causes the Fill command to bomb string SqlString = "SELECT TOP (1) Confession FROM Confessions";
// the next line works just fine //string SqlString = "SELECT TOP (1) Quote, Author FROM Quotes ORDER BY NEWID()";
// Create a connection and command object so we can interact with the database. using (OleDbConnection cn = new OleDbConnection (ConnectionString)) { using (OleDbCommand cmd = new OleDbCommand(SqlString, cn)) {
cmd.CommandType = CommandType.Text; cn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill (dt); // Program actually bombs on this line } }
return dt;
}
Below is the error I'm getting. Does this ring any bells?
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: OleDbDataAdapter internal error: invalid row set accessor: Ordinal=1 Status=UNSUPPORTEDCONVERSION. at System.Data.OleDb.RowBinding.CreateAccessor(IAccessor iaccessor, Int32 flags, ColumnBinding[] bindings) at System.Data.OleDb.OleDbDataReader.CreateAccessors(Boolean allowMultipleAccessor) at System.Data.OleDb.OleDbDataReader.BuildMetaInfo() at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Search.GetNextConfession() in f:Sourcedark humanitydh.webserviceApp_CodeSearch.cs:line 93 --- End of inner exception stack trace ---
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 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...
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??
I am using the SqlDependency to notify me of data changes in a table. However, as soon as I start it, the OnChange event fires over and over even though no data has changed.
Essentially All I want to do is be notified when records are inserted into a table. I am able to get another example to work using a Service Broker Queue and a sql query of WAITFOR ( RECEIVE CONVERT(int, message_body) AS msg FROM QueueMailReceiveQueue ) However I would prefer not to use a queue for once my messages have been read they are taken off the queue and I would rather control that manually.
Any help with getting SqlDependency to notify my app when records are added and not over and over when the data has changed would be great.
Have you ever tried to call the SqlDependency.Stop() method in a class destructor (C#)? It seems like the finalization process hangs after the call to the SqlDependency.Stop() method (for example the assignment after the SqlDependency.Stop() method call is never executed).
I tried to use the ADO.NET 2.0 tracing, and it shows that the SqlCommand.Cancel() method call throws an exception during finalization, but it€™s not possible to intercept it. Do you have any clue about it or have you ever experinced the same problem?
I created the table dbo.customer from the orginal table sales.customer. dbo.customer works but sales.customer does not. WHY? We took the time to design our tables under different schemas to help control, organize and assign permissions.Private Function GetCustomers() As DataTable Dim sqlconnection As New SqlConnection(WebConfigurationManager.ConnectionStrings("connectionstringAlliance").ConnectionString.ToString)WORKS:Dim command As New SqlCommand("SELECT CustomerID, CustomerType FROM dbo.Customer WHERE CustomerID=1234", sqlconnection)DOES NOT WORK:Dim command As New SqlCommand("SELECT CustomerID, CustomerType FROM Sales.Customer WHERE CustomerID=1234", sqlconnection)
Dim dependency As New SqlCacheDependency(command) Dim dad As SqlDataAdapter = New SqlDataAdapter(command) Dim customers As New DataTable dad.Fill(customers)Cache.Insert("Customers", customers, dependency) Return customersEnd Function Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim customers As DataTable = CType(Cache.[Get]("Customers"), DataTable)
If customers Is Nothing Then customers = GetCustomers() Label1.Text = System.DateTime.Now.ToString() End If gridviewCustomer.DataSource = customers.DefaultView gridviewCustomer.DataBind()
