Getting SQLDependency To Fire The OnChange
Oct 20, 2006
After many problems with permissions I have got got SQL to accept a notification request but the public static void OnChange(object sender, SqlNotificationEventArgs e) is never triggered. The notification registers and de-registers ok. The same connect string successfuly connects to the same database to process queries.
I can see the GUID suffixed stored procedure, queue and service being created. Where does SQL2005 store the address/name of the routine it is to trigger? (When the notification is cancelled, the guid-siffixed items disappear) I have looked at the generated stored procedure, queue and service, but there is no indication of what is to be called back.
I have followed the instructions at http://msdn2.microsoft.com/en-us/library/ms181122.aspx, but so far without avail. I have checked the Application and system event logs, but there is indication therein. Also the SQL log.
So my questions are:
1) Where is the callback stored (is it a pointer or an actual name) ?
2) What steps should I take to resolve this?
View 7 Replies
ADVERTISEMENT
Jan 4, 2008
Hi,
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!
Does anyone have a solution to this problem?
Thanks
/Jonas Djurback
View 7 Replies
View Related
Aug 2, 2006
I am running
ALTER DATABASE dbname SET ENABLE_BROKER
on my app startup and then SqlDependency.Start(), and then the following code
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT request_queue.track_id, track.file_name, track.track_number, track.track_name, " +
"artist.artist_id, album.album_id, artist.artist_name, album.album_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.
View 8 Replies
View Related
Mar 27, 2008
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.
Here is my code:
Code Snippet
public partial class Form1 : Form {
public static event OnChangeEventHandler OnChange;
string _strConnString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=email_queue;Pooling=False;";
string _strSql = "SELECT email_id from email where isprocessed = 0";
private DataSet dataToWatch = null;
private SqlConnection connection = null;
private SqlCommand command = null;
SqlDependency dependency = null;
SqlDataReader sdr = null;
public Form1() {
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e) {
SqlDependency.Stop(_strConnString);
SqlDependency.Start(_strConnString);
if (connection == null) {
connection = new SqlConnection(_strConnString);
connection.Open();
}
if (command == null) {
command = new SqlCommand(_strSql, connection);
}
if (dataToWatch == null) {
dataToWatch = new DataSet();
}
GetData();
}
private void GetData() {
dataToWatch.Clear();
command.Notification = null;
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
command.CommandTimeout = 400;
using (SqlDataAdapter adapter = new SqlDataAdapter(command)) {
adapter.Fill(dataToWatch, "email");
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e) {
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
if (i.InvokeRequired) {
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
i.BeginInvoke(tempDelegate, args);
return;
}
dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
this.Text = DateTime.Now.ToString();
GetData();
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e) {
SqlDependency.Stop(_strConnString);
if (connection != null) {
connection.Close();
}
}
}
View 4 Replies
View Related
Apr 24, 2008
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?
Thank you
Marina B.
View 1 Replies
View Related
Feb 7, 2007
I've wrote a component that take advantage of the query notification macjanizm, i use this component in a web service , and its working great, with a little problem , i have imporsonation enabled in my web service and a user and password is set in the identity node in web.config , the impersonation is working great and the web service is running under the user name i've set in the web.config , but, when i change a table in the database and the change event is raised in my application the user name is no more the user in the web.config...The user is now ASPNET ....
How can i solve this problem of user name change?
Thanks in advance ,
Eden
View 1 Replies
View Related
Mar 29, 2007
I have an sql dependancy setup on a particular table that is not updated all that often. When I load my application that has the dependancy code in it, the onChange event of the SqlDependancy fires over and over again non stop. I am positive I am not updating the information in the table I am querying so I can not figure out why it would do that. But I bet one of you knows! If you need code I can supply it and if you need anything else let me know. Thanks in advance.
View 9 Replies
View Related
Jul 17, 2007
Hi all,
I am investigating using Query Notifications - a great addition BTW. I have built a test app - loosely based on MSDN example - and am running against a SQL Express 2005 server. I have the following problem:
I have start/stop buttons to enable change checking, and a hardcode query that I am using for testing. If I stop and restart I now recieve duplicate notifcations, a single change causing the OnChange event to fires twice with two different ids Repeat this and the event will fires 3 times for each change and so on.
This only happens with a running app, if I restart the app I only get a single notification so I assume I have missed a step in stopping query notifcations or reinitialisation. I have include code below:
private bool Start()
{
try
{
// Remove any existing dependency connection, then create a new one.
SqlDependency.Stop(_currentConnectionString);
if (SqlDependency.Start(_currentConnectionString))
{
if (connection == null)
{
connection = new SqlConnection(_currentConnectionString);
}
if (command == null)
{
command = new SqlCommand(_sqlQueryString, connection);
}
return (true);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return false;
}
private void Stop()
{
SqlDependency.Stop(_currentConnectionString);
if (command != null)
{
command.Notification = null;
command = null;
}
if (connection != null)
{
connection.Close();
connection = null;
}
}
private void GetDataSnapshot()
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
// Create and bind the SqlDependency object
// to the command object.
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataToWatch, tableName);
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// This event will occur on a thread pool thread.
// Updating the UI from a worker thread is not permitted.
// The following code checks to see if it is safe to
// update the UI.
ISynchronizeInvoke i = (ISynchronizeInvoke)this;
// If InvokeRequired returns True, the code
// is executing on a worker thread.
if (i.InvokeRequired)
{
// Create a delegate to perform the thread switch.
OnChangeEventHandler tempDelegate = new OnChangeEventHandler(dependency_OnChange);
object[] args = { sender, e };
// Marshal the data from the worker thread
// to the UI thread.
i.BeginInvoke(tempDelegate, args);
return;
}
// Remove the handler, since it is only good
// for a single notification.
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
// At this point, the code is executing on the
// UI thread, so it is safe to update the UI.
++changeCount;
lblStatus.Text = String.Format(statusMessage, changeCount);
// Add information from the event arguments to the list box
// for debugging purposes only.
//lbChanges.Items.Clear();
lbChanges.Items.Add("+++++++++++++++++");
lbChanges.Items.Add("Id: " + dependency.Id);
lbChanges.Items.Add("Info: " + e.Info.ToString());
lbChanges.Items.Add("Source: " + e.Source.ToString());
lbChanges.Items.Add("Type: " + e.Type.ToString());
// Reload the dataset that is bound to the grid.
if (e.Info != SqlNotificationInfo.Error)
this.GetDataSnapshot();
}
Thanks, Nick
View 3 Replies
View Related
Mar 31, 2007
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
View 10 Replies
View Related
Oct 19, 2007
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(); }
}
Thanks
swapnil
View 4 Replies
View Related
May 30, 2007
Hello all,
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!
Mike
View 3 Replies
View Related
Sep 24, 2007
Hi everyone, Anyone know how SQLDependency works in a web farm setup? Which machine will receive the notification - All of them or just the machine that started the dependency?Marc
View 1 Replies
View Related
Oct 4, 2007
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:
[Data] [varbinary] (max) NULL
and the SELECT statement looked like:
private const string _select = "SELECT [Role_s], [User_s], [DataType], [Project_s], [DataName], [tag], [Data_s], [ScopeType], [ScopeOwner], [Data1], [Data2], [Data3], [Data4], [IsProject], [Data] FROM [ovs].[OVS_AppData_tbl] WHERE DataType='SchedTask'";
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:
[Data] [image] NULL
This may (or may) be relevant (vs. varbinary).
View 1 Replies
View Related
Nov 3, 2006
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:
With sql dependencies
RPC:Starting 2006-11-01 11:41:52.793 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
SP:Starting 2006-11-01 11:41:52.793 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
SP:StmtStarting 2006-11-01 11:41:52.793 select fullname from [dbo].[tblDoctor] where [dbo].[tblDoctor].doctorid = @id
QN: parameter table 2006-11-01 11:41:52.823 <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>LRU counter reset</qnev:EventText><qnev:TableId>1617492891</qnev:TableId></qnev:QNEvent>
QN: parameter table 2006-11-01 11:41:52.823 <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>reference count incremented</qnev:EventText><qnev:TableId>1617492891</qnev:TableId><qnev:RefCount>30293</qnev:RefCount>
QN: subscription 2006-11-01 11:41:52.823 <qnev:QNEvent xmlns:qnev="http://schemas.microsoft.com/SQL/Notifications/QueryNotificationProfiler"><qnev:EventText>subscription registered</qnev:EventText><qnev:SubscriptionID>106047</qnev:SubscriptionID></qnev:QNEvent>
Broker:Conversation Group 2006-11-01 11:41:52.823
Broker:Conversation 2006-11-01 11:41:52.823 STARTED_OUTBOUND
SP:StmtCompleted 2006-11-01 11:41:52.793 2006-11-01 11:41:52.843 select fullname from [dbo].[tblDoctor] where [dbo].[tblDoctor].doctorid = @id
SP:Completed 2006-11-01 11:41:52.793 2006-11-01 11:41:52.843 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
RPC:Completed 2006-11-01 11:41:52.793 2006-11-01 11:41:52.843 exec [dbo].[usp_tblDoctor_Select] @id=N'65'
Without SQL dependencies, the operation completes in less than the granularity of the measurement.
RPC:Completed 2006-11-01 11:47:52.810 2006-11-01 11:47:52.810 exec [dbo].[usp_deleteme_jerel] @id=N'7'
View 4 Replies
View Related
Jun 6, 2007
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.
I Suppose this can be achieve with SSB
Any help will be appreciated...
View 1 Replies
View Related
Jan 8, 2007
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.
Thanks, James
View 1 Replies
View Related
Aug 22, 2007
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:
private void InitSQLDependencies()
{
string connstr1 = €œData Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDb1€?;
string connstr2 = €œData Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDb2€?;
SqlDependency.Start(connstr1);
SqlDependency.Start(connstr2);
using(SqlConnection connection = new SqlConnection(connstr1))
{
string ssql = €œSELECT Id,Text FROM dbo.Table1 €œ;
SqlCommand command = new SqlCommand(ssql , connection);
SqlDependency dependency =new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
}
using(SqlConnection connection = new SqlConnection(connstr2))
{
string ssql = €œSELECT Id,Text FROM dbo.Table1 €œ;
SqlCommand command = new SqlCommand(ssql , connection);
SqlDependency dependency =new SqlDependency(command);
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 ?
View 19 Replies
View Related
Aug 30, 2007
Hello!
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.
Thanks!
John
View 15 Replies
View Related
Mar 24, 2006
I deleted the account owning Test Database after creating Test Database.
Everythings work well except SqlDependency.
SqlDependency OnChange Event is not working.
I have tested SQL 2005 ent RTM & VS 2005 Pro RTM.
I'm curious why it happened.
Thank you.
View 3 Replies
View Related
Apr 7, 2006
Folks,
Hoping you can help explain why I get this error.
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)
AddHandler mDependency.OnChange, AddressOf mDependency_OnChange
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?
CREATE TABLE [tblLog](
[pkLogId] int IDENTITY(1,1) NOT NULL,
[When] [datetime] NULL,
[fkEventId] tinyint NULL,
[WorkId] int NULL,
[fkWho] [bigint] NULL,
[What] [text] NULL,
[IsRTF] bit NULL,
PRIMARY KEY ( [pkLogId] ASC )
) TEXTIMAGE_ON [PRIMARY]
View 9 Replies
View Related
Sep 17, 2007
hello,
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...
Thanks very much,
Drew
View 4 Replies
View Related
Jan 3, 2008
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.
string connectstring = "Server=<SERVER>;Integrated security=true;database=<DATABASE>";
SqlDependency.Start(connectstring);
SqlDependency.Stop(connectstring);
5) Add using System.Data.SqlClient at the top of form1.cs
6) Run the app.
-Kent
View 4 Replies
View Related
Jul 7, 2006
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>
Thanks,
Ashish
View 8 Replies
View Related
Jun 6, 2006
Hi,
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!
Kind Regards,
Imran007
View 7 Replies
View Related
Jul 20, 2007
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>
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="10000">
<databases>
<add name="MyDB_40" connectionStringName="MyDBConnString_localdev" pollTime="2000" />
</databases>
</sqlCacheDependency>
</caching>
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?
q4: How can I test that it's properly installed?
View 3 Replies
View Related
Aug 2, 2007
Is the following behaviour by design?
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?
View 2 Replies
View Related
Jul 11, 2007
Hello,
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)
Many thanks
Travis
View 1 Replies
View Related
Sep 18, 2007
Hello,
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??
Thanks
View 3 Replies
View Related
Aug 16, 2006
Hey guys,
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).
~Program()
{
if (!_finalized)
{
SqlDependency.Stop(NocConnectionString);
_finalized = false;
}
}
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?
Regards,
Dmytro Kryvko
View 10 Replies
View Related
Dec 4, 2007
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()
End Sub
View 6 Replies
View Related
Aug 14, 2006
Hi Folks I hope every thing good.I have this problem I have a master page and an aspx page In aspx page there is an SqlDataSource Componant that call a storage prcedure from db that insert value to a certain table The issue here that he call the sp twice so the row record twince in the database What is the problem ?Thanks Bye
View 4 Replies
View Related
May 15, 2008
Hi
I have a some confusion pleaz resolve it
can i fire truncate to the multiple tables if you have any type of syntax then tell me and if not then could u resolve from store procedure
Ashish
View 1 Replies
View Related
Sep 21, 2005
Is it possible to create a trigger to monitor when a value changes from 1 to 0 in a particular row.
Example, rows have names, pins and status columns. When the status column of a row changes from 1 to 0, I am going to send a mail item to the person whose name has had the status changed.
Mike
View 1 Replies
View Related
