SqlDependency OnChange Event Fires Repeatedly

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


ADVERTISEMENT

SqlDependency OnChange Problem

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

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 View Related

SqlDependency.OnChange() Not Firing

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

SqlDependancy OnChange Event Problem

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

SqlDependancy OnChange Event Triggers Over And Over Again

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

Datsource Delete Event Fires - Why???

Mar 17, 2008

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-----------------------------------------------------------------------------------

View 3 Replies View Related

Button Click Event Fires While Refresing The Page

Aug 9, 2007

Hi!

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.

Please help me out in this issue.
Thanks

View 1 Replies View Related

Question/doubt On SqlDependency.Start/SqlDependency.Stop

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

Using The Same View Repeatedly

Jul 23, 2005

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.......

View 2 Replies View Related

Sqlserver Pwning Me Repeatedly

Mar 19, 2007

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.

Thanks
- Brandon

View 2 Replies View Related

Repeatedly Starting Databases

Mar 3, 2004

Hi all,

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?

Thanks in advance.

Duncan

View 5 Replies View Related

Query Notification - Duplicate OnChange Events Fired

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

Trigger Fires When?

Jan 19, 2000

If I update a record such as

Update MyTable SET MyField = 'MyValue'
where MyKey = 'Key1'

The trigger fires .....

If I update multiple records

Update MyTable SET MyField = 'MyValue'
where MyKey IN ('Key1','Key2')

Does the trigger (Update) on MyTable fire more than once or once.

View 4 Replies View Related

Transact SQL :: Repeatedly Assign Set Of Dates To Each ID?

Nov 18, 2015

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)

Insert sample  values (1,'01/01/2012' ,'Tom'),
(2,'01/10/2012','Sam'),
(3,'02/15/2012','John')

View 2 Replies View Related

Temp Table For Repeatedly Used Sub Query

Nov 3, 2007

Hi,

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..

Thanks in Advance..

View 2 Replies View Related

Trigger Fires In One Db And Populate Another Db

Apr 12, 2007

hi,

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;


WHERE:

USER_DATA_DEVIQ = b_bbb_b
AUSADFORMULA02 = bbb
QDB_KS = b_bbb
USER_DATA = a_aaa_a
AUSSMEDEVIQ1 = aaa
QDB_Test = a_aaa

END
GO

View 9 Replies View Related

SQL 2005 SP2 Failes Repeatedly - SQLServer2005SP2-KB921896-x86-ENU

Oct 22, 2007

SQL 2005 SP2 Failes repeatedly - SQLServer2005SP2-KB921896-x86-ENU

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...

-Tim from Fairfax

View 1 Replies View Related

UpdateBatchSize Fires Individual Statements

Jun 30, 2006

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

View 2 Replies View Related

Trigger Only Fires In Enterprise Manager

Nov 21, 2007

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.


Any ideas why?

TIA.

View 13 Replies View Related

Dynamic Security Stored Procedure Repeatedly Called

Jan 26, 2007

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?

View 20 Replies View Related

SQL Server Trigger That Fires Only On Update Of Certain Field?

Aug 14, 2006

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!

View 2 Replies View Related

Recovery :: Configure Extended Event To Trigger A Mail Whenever Any Event Occurs

Jun 2, 2015

Recently we migrated our environment to 2012.

We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.

Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.

I have gone through so many websites but i never find.

View 13 Replies View Related

DB Engine :: Event Tracing For Windows Failed To Send Event

Oct 25, 2011

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.

View 2 Replies View Related

WMI Event Watcher Task Continual Firing Event When Not Triggered

Apr 8, 2008

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 ?

View 2 Replies View Related

SQL Server 2005 Trigger Fires Per Statement Or Per Record

May 3, 2006

Hi

In SQL Server 2005, does TRIGGER for DML fire per statement or per Record ?

If both, can we define the way it should fires.

thanks / ramanuj

View 11 Replies View Related

Installing SQL Server 2005 Management Tools - Setup Crashing Repeatedly

Mar 18, 2008

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.


Time : Tue Mar 18 12:19:20 2008


List of log files:
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_Core(Local).log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_SQLSupport_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_SQLNCLI_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_OWC11_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_BackwardsCompat_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_MSXML6_1.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_Datastore.xml
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_.NET Framework 2.0.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_Core.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_.NET Framework 2.0 LangPack.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_.NET Framework Upgrade Advisor.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_.NET Framework Upgrade Advisor LangPack.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_.NET Framework Windows Installer.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_.NET Framework Windows Installer LangPack.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_SNAC.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_Support.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_SCC.log
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_EOC429_WI.log

View 3 Replies View Related

Help - Security Event Log Posts Error Event ID 560 Every Few Seconds

May 31, 2007

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

Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015



Applied the following fix

http://support.microsoft.com/kb/907460/


Now we are getting



Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
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,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D

View 4 Replies View Related

Query Or Script To Get The Event Viewer Event Properties?

Nov 2, 2007



Hi all,


Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?

Scenario:

>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application

click on one event.We can get the log info which is the manual procudure.

But now i want to get the event properties through the Query analyzer...

Any help would be great?


Thanks,

View 4 Replies View Related

Proc With Dynamic Query Hang When Insert Trigger Fires....

Feb 16, 2005

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?

thanks
David

View 4 Replies View Related

SQL 2012 :: Maintenance Plan - Rebuild Indexes Only Run If Required Or Open Fires On Schedule?

Aug 23, 2015

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.

View 2 Replies View Related

SQLDependency

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

SqlDependency

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







Copyrights 2005-15 www.BigResource.com, All rights reserved