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.
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!
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.
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 ....
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.
I have "sqlGetReservationMembers" datasource in my ascx code and another datasource in 'KillReservation'. As you can see "sqlGetReservationMembers" uses a SELECT command only. I have verified this several times. The 3rd block below is the only place in code-behind where "sqlGetReservationMembers" is referenced. When the "KillReservation" fires I get an error stating "Deleting is not supported by data source 'sqlGetReservationMembers' unless DeleteCommand is specified". I'm confused because I can't see where the 'sqlGetReservationMembers' delete event is being fired. I have removed the datasource and recreated it but the problems returns. What am I missing? Thanks <asp:SqlDataSource ID="sqlGetReservationMembers" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>" SelectCommand="sp_GetReservations" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="gvMyRides" Name="ID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>--------------------------------------------------------------------------------- Protected Sub KillReservation(ByVal RideID As Integer, ByVal MemberID As Integer) Using Myconnection As New SqlConnection(Config.GetConnectionString("SiteSqlServer")) Dim Mycommand As New SqlCommand("sp_KillReservation", Myconnection) Mycommand.CommandType = CommandType.StoredProcedure Mycommand.Connection.Open() Mycommand.Parameters.Add(New Parameter("@MemberID", TypeCode.Int32, MemberID)) Mycommand.Parameters.Add(New Parameter("@RideID", TypeCode.Int32, RideID)) Mycommand.ExecuteNonQuery() Myconnection.Close() End Using End Sub----------------------------------------------------------------------------------Protected Sub gvMyRides_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvMyRides.SelectedIndexChanged sqlGetReservationMembers.SelectParameters.Item("ID").DefaultValue = Me.gvMyRides.SelectedValue Me.gvReservationList.DataSourceID = Me.sqlGetReservationMembers.ID ..More Code.......... End Sub-----------------------------------------------------------------------------------
I have written some code to insert a record into the table in BtnAdd click event and I also have a Grid view control to show the table records.
If I click the Add button ,the record gets inserted into the table and shown in the grid correspondingly.But if I refresh teh page,the same data gets inserted again since it fires the Btnclick Event.
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?
Hi.A question I have is with regard to the use of views with SQL2000. IfI have a view called "A_view" and used in the following manner;----------------SELECT ...FROM A_ViewWHERE ....UNIONSELECT ....FROM A_ViewWHERE .....-----------------is the view computed twice? Ideally if the view is computationallyexpensive I would rather it was only computed once.Also this would be preferred for data consistency.Is there a way to ensure the view is only computes once?Regards JC.......
Hey all. right now I've been spinning my wheels for almost 2 days on a problem with c# 1.1/sqlserv 2005...
I have a dataset that's filled with data that I'm trying to write to a database, I've ensured that the dataset is fine.
I do a transaction to write the code to the table, an insert command, and an update command.
then commit the transaction
Everything seems to work fine, but when I go look at the table, it doesn't actually write any rows!
The weird thing is deletion of the rows works fine on the same table.
I look at the sqlserver tracer and watch it do the deletion on the table, skip right over the insert statements (ie: no insert statements show up whatsoever in the tracer) then commit the transaction.
If anyone has ever heard of something like this happening i would really appreciate some advice. I'm trying to be as specific as I can about the problem, and I've been spinning my wheels for almost 2 days without avail.
I've tried making other tables and writing to them, getting the same results. I think it's somehow not referencing everything correctly but again, everything seems like it's fine.
I've been handed a SQL Server that is used as an MIS source. There are 4 databases that carry out the task of importing data from various sources, then manipulting that data, and offering the data for reporting purposes.
The vendor has also created several other databases (of which there are also 4), but no-one in my company seems to know the purpose of these dbs.
In the logs, there are approximately 8/9 messages per second - not every second, but numberous seconds per minute - stating....
Starting up database 'db_name'.
... each time, all 4 of the mysterious dbs appear.
I've checked the spid that is running this job this morning, and it seems to be NT AUTHORITYSYSTEM connected to one of the original 4 report databases.
Does this have any affect on the performance of the server, or the specific db attached to the user?
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))
I have a table with 3 columns , let say (PatientID int, AppointmentDate date, PatientName varchar(30))
My source data looks in below way..
PatientID AppointmentDate PatientName 1 01/01/2012 Tom 2 01/10/2012 Sam 3 02/15/2012 John
I need output in below way..
PatientID AppointmentDate PatientName 1 01/01/2012 Tom (actual patient record) null 01/10/2012 Tom null 02/15/2012 Tom null 01/01/2012 Sam 2 01/10/2012 Sam (actual patient record) null 02/15/2012 Sam null 01/01/2012 John null 01/10/2012 John 3 02/15/2012 John (actual patient record)
I need t-sql to get above output. Basically the appointment dates are repeatedly assigned to each patient but only difference is patientid will be not null for actual patient record.
Create table sample (PatientID int null, AppointmentDate date null, PatientName varchar(30) null)
Here is my query which lists all orders for products supplied by Supplier-3. A typical Query on the Northwind database i wrote is like this..
Select * FROM [Order Details] WHERE ProductID in (Select ProductID From Products where SupplierID = 3)
The subquery in Red was used in multiple places in one of my Stored Procedures..
So what i thought was - use a temp table to store the resultset from this subquery, and then use the temp table instead of querying the Products table everywhere..
My Query looked something like this..
Declare @ProductIDs TABLE (ProductID int)
INSERT INTO @ProductIDs Select ProductID From Products where SupplierID = 3
Select * FROM [Order Details] WHERE ProductID in (Select ProductID FROM @ProductIDs)
Well, I expected an increase in performance with the latter approach, but seems my Stored Procedure is taking more time with the second solution..
Would be glad to see ne explanation on this behavior..
I want a trigger in db aaa to fire when table a_aaa is updated in server a and table b_bbb in db bbb in server b to be populated with data. I know how to write a trigger if fired and the result stays in one server with one db. But I don't know how to do it if between two servers and two db.
a win server 2003 standard Edition sql Server 2000 db:aaa table: a_aaa column:a_aaa_a
b win server 2003 standard Edition sql Server 2000 db:bbb table:b_bbb column: b_bbb_b
It's not working because the symtax is incorrect and because I am not sure how to do it between two servers. If it is not correct, where am I wrong? Where should each line be located?? et cetera...... Can anyone help?
Thanks in advance.
CREATE TRIGGER [enddate_changed_on_alert] ON [dbo].[USER_DATA]
FOR INSERT, UPDATE AS
BEGIN SET NOCOUNT ON;
DECLARE @EndDate as DATE DECLARE @CompCode as VARCHAR(15) DECLARE @PhoneGMSM as VARCHAR(10) DECLARE @PhoneALERT as VARCHAR(10)
SELECT @PhoneALERT=phone1 from AUSSMEDEVIQ1.QDB_Test.USER_DATA; SELECT @PhoneGMSM=PHONE1 from AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
UPDATE AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ SET EXT4 = "111" WHERE AUSSMEDEVIQ1.QDB_Test.USER_DATA.@Phone = AUSADFORMULA02.QDB_KS.dbo.USER_DATA_DEVIQ;
Failed install on several Win 2003 Ent Ed., 32bit servers both named and default instances, both upgrades and direct installed versions. Why would MS put out such a riddle?
Error Message is " A recently applied update, KB921892, failed to install.
Also, confoundingly, the error log for the hotfix indicates ""9.00.3042.00 while the update version is: 9.00.2047.00."
But the version display on the properties of one of the servers is: "Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) "
Any help or comments are deeply appreciated, as the seems to be impacting DB Maintenance Plans, etc...
In Pablo Castro webcast, First Look at ADO.NET 2.0, he mentions the use of UpdateBatchSize which I think would be handy.However, I was not able to get it to work.
Dim t As New Global.System.Data.SqlClient.SqlDataAdapter("Select * from Table_1", Global.System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString)Dim d As New Global.System.Data.DataTablet.FillSchema(d, SchemaType.Source)Dim cb As New Global.System.Data.SqlClient.SqlCommandBuilder(t)For c As Int32 = 1 To 10000Dim r As Global.System.Data.DataRow = d.NewRowr("id") = Global.System.Guid.NewGuidr("val") = CType(Rnd(), Int32)d.Rows.Add(r)Nextt.UpdateBatchSize = 100t.Update(d)d.Dispose()t.Dispose()
What ends up showing in Sql Server Profiler (Sql Server 2005) is each Insert being executed in it's own statement:exec sp_executesql N'INSERT INTO [Table_1] ([id], [val]) VALUES (@p1, @p2)',N'@p1 uniqueidentifier,@p2 int',@p1='3793CB5E-3B7A-45E7-9A53-0BD528BB6B07',@p2=1 I think I made this example very simple and yet can't fathom why it won't batch the statements. I'm somewhat aware of SqlBulkCopy and was very pleased with that speed, but don't think it would handle the Update,Delete,Insert that the SqlDataAdapter.Update would. Originally started using xsd DataSets until I saw the data tutorials in the Learning section here in which case I copied out the autogenerated TableAdapter classes and fuddling with them to do what I want since batching was not something I saw in TableAdapters. Does anyone see what I'm missing here?Nathan
I have an update trigger that works wonderful as long as I am updating the row in Enterprise Manager, but if I update the same column and row using an update statement in Query Analyzer the trigger doesn't fire.
I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?
Dear Experts,I'm an Oracle guy, who is being given more SQL Server assignmentslately.I've been looking for things on the web about this, but I can'tanything so far.In Oracle, I you can create a trigger on a table that -only- fires ifcertain fields are updated.create or replace trigger trg_some_triggerBEFORE insertOF some_field1, some_field2on tbl_some_tablefor each row....Is this also possible in SQL Server?What is the syntax please?Thanks a lot!
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date 10/25/2011 10:55:46 AM Log SQL Server (Current - 10/25/2011 10:55:00 AM) Source spid Message Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file. The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30 WHERE targetinstance isa 'CIM_DataFile' AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message. However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
I'm having many many issues installing sql server management tools. i had visual studio 2005 installed first, but uninstalled and sql related things before trying to install sql server management tools again - i also deleted the program files/microsoft sql server/ folder so there are no references.
Firstly the system configuration check gives me a warning that the system doesn't meet the recommended hardware requirements - this is wrong... i've got 2.33Ghz dual core + 1gb of ram...
I select just management tools + client connectivity to install and click next -> the setup support files/native client/owc11 etc all install fine but workstation components etc fail and the setup log appears to either be empty and not available and MSXML6 fails... after clicking finish the installer appears to crash - : "Microsoft SQL Server 2005 Setup has encountered a problem and needs to close. We are sorry for the inconvenience"... I have tried all sorts of variations on this install and have had no problems in the past - please help!
The setup log from the MSXML6 failure - === Verbose logging started: 18/03/2008 12:34:09 Build type: SHIP UNICODE 3.01.4000.4039 Calling process: C:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe === MSI (c) (5C:78) [12:34:09:067]: Resetting cached policy values MSI (c) (5C:78) [12:34:09:067]: Machine policy value 'Debug' is 0 MSI (c) (5C:78) [12:34:09:067]: ******* RunEngine: ******* Product: {AEB9948B-4FF2-47C9-990E-47014492A0FE} ******* Action: ******* CommandLine: ********** MSI (c) (5C:78) [12:34:09:067]: Client-side and UI is none or basic: Running entire install on the server. MSI (c) (5C:78) [12:34:09:067]: Grabbed execution mutex. MSI (c) (5C:78) [12:34:09:067]: Cloaking enabled. MSI (c) (5C:78) [12:34:09:067]: Attempting to enable all disabled priveleges before calling Install on Server MSI (c) (5C:78) [12:34:09:067]: Incrementing counter to disable shutdown. Counter after increment: 0 MSI (s) (28:E4) [12:34:09:113]: Grabbed execution mutex. MSI (s) (28:74) [12:34:09:113]: Resetting cached policy values MSI (s) (28:74) [12:34:09:113]: Machine policy value 'Debug' is 0 MSI (s) (28:74) [12:34:09:113]: ******* RunEngine: ******* Product: {AEB9948B-4FF2-47C9-990E-47014492A0FE} ******* Action: ******* CommandLine: ********** MSI (s) (28:74) [12:34:09:113]: Machine policy value 'DisableUserInstalls' is 0 MSI (s) (28:74) [12:34:09:113]: MainEngineThread is returning 1605 MSI (c) (5C:78) [12:34:09:113]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1 MSI (c) (5C:78) [12:34:09:113]: MainEngineThread is returning 1605 === Verbose logging stopped: 18/03/2008 12:34:09 ===
The log summary:
Microsoft SQL Server 2005 9.00.1399.06 ============================== OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600) Time : Tue Mar 18 12:05:06 2008
EOC429 : The current system does not meet recommended hardware requirements for this SQL Server release. For detailed hardware requirements, see the readme file or SQL Server Books Online. Machine : EOC429 Product : Microsoft SQL Server Setup Support Files (English) Product Version : 9.00.1399.06 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_SQLSupport_1.log -------------------------------------------------------------------------------- Machine : EOC429 Product : Microsoft SQL Server Native Client Product Version : 9.00.1399.06 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_SQLNCLI_1.log -------------------------------------------------------------------------------- Machine : EOC429 Product : Microsoft Office 2003 Web Components Product Version : 11.0.6558.0 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_OWC11_1.log -------------------------------------------------------------------------------- Machine : EOC429 Product : Microsoft SQL Server 2005 Backward compatibility Product Version : 8.05.1054 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_BackwardsCompat_1.log --------------------------------------------------------------------------------
SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup) We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers. We were having
Event Type: Failure Audit Event Source: Security Event Category: Object AccessEvent ID: 560 Date: 5/23/2007 Time: 6:27:15 AM User: domainuser Computer: MACHINENAME Description: Object Open: Object Server: SC Manager Object Type: SC_MANAGER OBJECT Object Name: ServicesActive Handle ID: - Operation ID: {0,1623975729} Process ID: 840 Image File Name: C:WINDOWSsystem32services.exe Primary User Name: MACHINE$ Primary Domain: Domain Primary Logon ID: (0x0,0x3E7) Client User Name: User Client Domain: Domain Client Logon ID: (0x0,0x6097C608) Accesses: READ_CONTROL Connect to service controller Enumerate services Query service database lock state
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter. ----------------------------- declare @stringSQL varchar(200) select @stringSQL= 'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0' print @stringSQL_GetUserName exec (@stringSQL_GetUserName) select * from temp1 ------------------------------ above dynamic query works fine. Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang... Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
Normally we use rebuild, reorganize indexes when it is required, I used a SQL job using maintenance plan to run daily and rebuild, reorganize indexes and update statistics but I do not know if it runs either they are required or not. Should this plan automatically execute the build upon required indexes to be rebuild or it fires either they are required to be executed or not.
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(); } }