Reacting To DB Events
Dec 16, 2005
I'd like to build an application that will react to specific changes to
data in a set of tables in a database. The application would replicate
these data changes to another database. The target database won't be
SQLServer. Neither is this simple replication, at times the application
will need to get extra data from the source database before the target
is updated.
In other DBMS systems I am involved with the DBMS has the facilty to
write to an application message queue so that the monitoring application
only has to monitor the queue rather than a database. What I'd like to
do is something like this:
1. Some application changes data in a table.
2. A trigger reacts to the change and writes a message to an application
queue.
3. A windows service/process monitors the queue and picks up the
message. It then carries out whatever replication/DB actions are necessary.
This would mean defining a number of new triggers on existing tables and
developing the windows service/process. Existing applications and the
existing tables in the database would remain unchanged. I'm not a
windows programmer but I have someone in my team who is and who will
build the windows service/process.
The bit I'm unsure about is how a trigger can write to an application
queue or communicate with the windows service/process. I may be using
the wrong terminology as I have more knowledge of Unix than Windows.
Could anyone help with how I can do this or suggest any alternative
strategies.
Thanks In Advance.
Laurence
View 3 Replies
ADVERTISEMENT
Mar 5, 2002
Hello All
I have queries which take over 30 secs to run, which I wish to monitor.
Currently, I am monitoring using sql profiler.
Is there any way of setting up mail to e-mail me when such a query happens.
Could I set up an Alert, or is there some other method.
I want to be able to react to these events faster before the users complain.
I am using sql server 7 enterprise and I have exchange set up.
Any help would be great
Pargat
View 2 Replies
View Related
Jun 20, 2007
Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.
View 1 Replies
View Related
Jan 28, 2006
I have a start date, end date for each event.
I want to list all events between the start and end date comes in Saturday or Sunday.
in SQL server 2005 TSQL statement.
any insights ?
View 1 Replies
View Related
Feb 3, 2014
I am using this code for LongRunning Queries.
CREATE EVENT SESSION LongRunningQuery
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 60000
[Code] ...
Here Instead of writing to XML file how can send an EMAIL if a query runs more than 1 minute in my server ?
View 2 Replies
View Related
Mar 20, 2004
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
View 1 Replies
View Related
May 29, 1999
What we are trying to do is watch a table in one database for an insert. If something is inserted into a unique ID column, we want to go to another database, on the same server, and look for that same number and place a value in another column for that record. Any ideas?
View 1 Replies
View Related
May 16, 2008
as asap pl give the detailed and clear description reg differences between delegates and events
vedavathi zend
View 3 Replies
View Related
Aug 30, 2006
Just finishing of a large ETL system and have aquestion about the following:-
We have 164 child packages being called from a single parent package which is setup to perform logging to a SQL Server table. Anything that Errors or has Warnings is logged accordingly, however, how do you trap the PackageStart and PackageEnd events? when the child package knows nothing about logging?
My first thought was to have the Parent call the AddEvent SP with the appropriate values, but thought I may check here in case I missed something
View 5 Replies
View Related
Aug 30, 2007
Hi,
I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...
Another issue, if i omit an error on a transformation object ( in order let the flow continue executing), can this error be managed by an event or record it in a log?
Thanks
View 6 Replies
View Related
Oct 25, 2007
Hi
I would like to know how I can get hold the text for a service and event notifications.
What I need is the ability to get hold of a list of the events I'm currently monitoring.
i.e.
Server or database level
I though I would be able to get hold of this in sys.server_event_notifications but it doesn't seem to have any data although my server level event notifcation service is running.
Any help?
TIA
View 1 Replies
View Related
Nov 17, 2006
I have the following code and I'm trying to set an event dynamicly in the foreach statement...
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]);
SqlCommand select = new SqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn);
SqlDataAdapter da = new SqlDataAdapter(select);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
Table table = new Table();
foreach (DataRow dr in ds.Tables[0].Rows)
{
TableRow tr = new TableRow();
for (int i = 0; i < dr.ItemArray.Length; i++)
{
TableCell tc = new TableCell();
tc.Text = dr.ItemArray.GetValue(i).ToString();
tc.BackColor = Color.White;
if (dr.ItemArray.GetValue(8).ToString() == "COMPLETE")
{
tc.BackColor = Color.BlueViolet;
}
tr.Cells.Add(tc);
}
table.Rows.Add(tr);
}
foreach (TableRow tablerow in table.Rows)
{
TableCell newtc = new TableCell();
Button mybutton = new Button();
newtc.Controls.Add(mybutton);
mybutton.Text = "details";
//set the event here
tablerow.Cells.Add(newtc);
}
Panel1.Controls.Add(table);
DataBind();
View 2 Replies
View Related
May 16, 2006
I know how to get the events that start say on May, and I know how to get the events that end on May, however, How would I get the events that start on January and end in July. The month of May should display that event too.
so far, as an example, I have: SELECT
Events.startDate,
Events.endDate
FROM Events
WHERE
Events.Active = 1
AND
startDate BETWEEN convert(smalldatetime, '5/1/2006') AND convert(smalldatetime, '5/31/2006')
ORDER BY Events.startDate ASC; thank in advance.
View 3 Replies
View Related
Apr 29, 2004
I am not sure if I have posted something about this, but I can't believe there is not a single person out there that is using a procedure written in SQL in order to schedule sending of an email, if data hasn't been submitted.
I have had some hits and some help from some people but I am pretty much still stuck.
How is everybody else doing these sorts of thing,scheduling an email to be sent to a user if he/she hasn't submitted data,somebody must be doing it?
A sample, help ,anything???
Regards
View 12 Replies
View Related
Jul 23, 2005
I am tracing a SQL Server 2000 production server that gets a queryabout every second. The Event I chose to watch was "Lock:Timeout". Tomy surprise I see many of these come through the trace. Is this normalbehavior? What is "Lock:Timeout" showing me?Thanks,John
View 2 Replies
View Related
Mar 5, 2007
Hi,I am trying to debug some queries that are being generated by anexternal program, and I have no way of finding out what the actualsyntax of the query is from within the program itself. This means thatwhen the query fails I am left with only a very unhelpful message.So what I was wondering, was whether there is a way that I can view theactual queries that are sent to SQL server as they happen, or if areal-time solution doesn't exist, then some way I can look back afterthe event and see the syntax of the queries?Ideally, what I would like to do is see the queries in the form theywere sent to the server, ie "SELECT * FROM foo WHERE bar='foobar'", asthis would help me to figure out where the generated queries are goingwrong.Cheers,--Dylan Parryhttp://electricfreedom.org | http://webpageworkshop.co.ukThe opinions stated above are not necessarily representative ofthose of my cats. All opinions expressed are entirely your own.
View 5 Replies
View Related
Jul 20, 2005
Hiwe are considering porting an application from Ingres to SQL Server.Part of the application uses a feature of Ingres called databaseevents. These allow a application to monitor for an event happening inthe database, e.g. a user enters a record meeting certain conditions,the database raises an event including a record ID, and a clientapplication sees the event (without continual explicit polling), readsthe new record and starts processing it.Any idea what the equivalent functionality would be in SQL Server2000, or are applications simply not done that way?TIAChloe CrowderThe British Library
View 5 Replies
View Related
Jul 20, 2005
I'm trying to add functionality to a VB 6 application allowingcustomer service to add a customer number to a new customer.Customers are added to the database by sales personnel, and aprospective customer may have multiple rows due to projected ordersfor multiple products. Customer numbers are assigned when a newcustomer makes their first order.I'm using a DataGrid connected to an ADO Data Control. The datacontrol is connected to a view in SQL Server using the 'distinct'directive (I know it's not updatable) to show only one line per newcustomer. What I wish to do is capture the update event (probablythrough the FieldChangeComplete routine of the data control), manuallyassign the newly entered customer number to all appropriate rows inthe database, and cancel the update event with no notifications.I'm having two problems:1. I can't capture the newly entered customer number. The Textproperty of the DataGrid returns the old value of the cell instead ofthe newly entered value. How do I get the edited value?2. Even though I set adStatus = adStatusCancel in theFieldChangeComplete routine, I get a Microsoft DataGrid Control dialogstating 'Operation was Canceled'. How do I avoid this notification?
View 1 Replies
View Related
Feb 13, 2006
How is everyone using the logging features of SSIS? What events are relevant to day-to-day operation? Currently we are logging all events except for Diagnostic to a SQL table, and then using event log reporting. What would be the appropriate events used for a production environment?
View 1 Replies
View Related
Jun 17, 2015
Need calculating the count of events on each day. I have an event log table as shown below.Â
DECLARE @EventLog TABLE ( event_id int, status_id varchar(20) , dttm datetime )
INSERT INTO @EventLog
SELECT '100','Inplace','01/05/2015 05:00' UNION ALL
SELECT '100','Removed','03/05/2015 10:00' UNION ALL
SELECT '100','Inplace','05/05/2015 10:41' UNION ALL
[Code] ....
I need an output to look like on each day how many events were in place.
E.g.: for the month on May , Event_id 100 was inplace on 1st and removed on 3rd , so on 1st may 2 event_ids (100, 103)Â hence 2 .Â
event_ids - 103 / 104 and inplace the whole month and not removed. so its calculated the count as 2 til towards teh end of the month.Â
So I need to count of all each event_id in place for each day in may.Â
How to calculate the missing dates to ...
View 7 Replies
View Related
Sep 6, 2007
Hi,
Ive been taking a look at how to consume events from a package when executing programatically.
Ive got some code (copied below) that creates a package programatically, adds a sequence container then within that adds a script task , then executes it using the overloaded method of Package.Execute() that takes an IDtsEvents argument.
My class that implements IDtsEvents simply output a message to the console for each event type.
Weird thing is, when I execute, this is the only output I get:
Starting...
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Package
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Sequence
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.TaskHost
OnPostValidate:Microsoft.SqlServer.Dts.Runtime.TaskHost
OnQueryCancel
Package ran successfully
What I find weird is that I dont get information for loads of other event types. I would at least have expected to see some OnPostExecute events.
Anyone know why i dont see all of the events?
Thanks
Jamie
Heres the code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
namespace Package_API
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting...");
Package p = new Package();
p.InteractiveMode = true;
p.OfflineMode = true;
// Add a Script Task to the package.
Sequence s = (Sequence)p.Executables.Add("STOCK:Sequence");
TaskHost taskH = (TaskHost)s.Executables.Add("STOCK:ScriptTask");
// Run the package.
DtsEvents events = new DtsEvents();
p.Execute(null,null,events,null,null);
//p.Execute();
if (p.ExecutionResult == DTSExecResult.Failure || p.ExecutionStatus == DTSExecStatus.Abend)
Console.WriteLine("Package failed or abended");
else
Console.WriteLine("Package ran successfully");
Console.ReadLine();
}
}
}
// Class that implements the IDTSEvents interface:
public sealed class DtsEvents : IDTSEvents
{
void IDTSEvents.OnPreExecute(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPreExecute: " + exec.ToString());
}
void IDTSEvents.OnBreakpointHit(IDTSBreakpointSite breakpointSite, BreakpointTarget breakpointTarget)
{
Console.WriteLine("OnBreakpointHit");
}
void IDTSEvents.OnCustomEvent(TaskHost taskHost,string eventName,string eventText,ref Object[] arguments,string subComponent,ref bool fireAgain)
{
Console.WriteLine("CustomEvent");
}
void IDTSEvents.OnPreValidate(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPreValidate: " + exec.ToString());
}
void IDTSEvents.OnPostValidate(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPostValidate:" + exec.ToString());
}
void IDTSEvents.OnWarning(DtsObject source,int warningCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)
{
Console.WriteLine("OnWarning");
}
void IDTSEvents.OnInformation(DtsObject source,int informationCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError,ref bool fireAgain)
{
Console.WriteLine("OnInformation");
}
void IDTSEvents.OnPostExecute(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPostExecute");
}
bool IDTSEvents.OnError(DtsObject source,int errorCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)
{
Console.WriteLine("OnError");
return true;
}
void IDTSEvents.OnTaskFailed(TaskHost taskHost)
{
Console.WriteLine("OnTaskFailed");
}
void IDTSEvents.OnProgress(TaskHost taskHost,string progressDescription,int percentComplete,int progressCountLow,int progressCountHigh,string subComponent,ref bool fireAgain)
{
Console.WriteLine("OnProgress");
}
bool IDTSEvents.OnQueryCancel()
{
Console.WriteLine("OnQueryCancel");
return true;
}
void IDTSEvents.OnExecutionStatusChanged(Executable exec,DTSExecStatus newStatus,ref bool fireAgain)
{
Console.WriteLine("OnExecutionStatusChanged");
}
void IDTSEvents.OnVariableValueChanged(DtsContainer DtsContainer,Variable variable,ref bool fireAgain)
{
Console.WriteLine("OnVariableValueChanged");
}
}
View 4 Replies
View Related
Nov 16, 2006
I ran into a pretty bizzare behavior in SSIS:
I am trying to set up a package with a built-in auditing. It has a OnPreExecute, OnPostExecute and OnError event handlers. I am trying to record when the package starts, completes, and the completion status. Each one of these event handlers has a script task that does the logging. I put in debug message boxes into each event handler script to understand what goes on. So here's the sequence of events:
1. When starting the package the OnPreExecute event fires. Right away it fires the second time. I'm guessing what happens here is the script task within the event handler fires its own OnPreExecute event - that's how the first message really pops up. The second message is generated by the actual package-level OnPreExecute event.
2. I have a condition within the OnPreExecute event handler which might set the task status to failure. You would expect the OnError handler to fire, right?.. Wrong! The package dies without firing either OnError or OnPostExecute event....
3. If i remove the condition in step 2, and force an error in the package body, i get an OnError event, and then 2 OnPostExecute events ( i guess for the same reason as in step 1).
What I'm trying to understand is why in the world my OnPreExecute and OnPostExecute events get fired by their own event handlers, yet when i fire other events within these event handlers the appropriate (other) event handler does not run.
Any ideas will be greatly appreciated.
View 9 Replies
View Related
Sep 1, 2006
Hi everyone,
I was wondering what kind of events fires when you do a LoadFromSqlServer method??? I've got defined a Events class but debugging with F11 never get in touch with any event (apparently or mabye the problem
is that I haven't declared that concrete one)
Let me know if possible or not. I provide you my snippet of code:
Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package 'Para que no choque con el objeto APPLICATION DE SYSTEM.WINDOWS.FORMS
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim EventsSSIS As Eventos
EventsSSIS = New Eventos()
..
..
pkg = app.LoadFromSqlServer(ObjSSIS.sRutaDts & "" & ObjSSIS.sSSISName, ObjSSIS.sServer, "usrSSIS", "aaa", EventsSSIS)
..
..
Public Class Eventos
Implements IDTSEvents
OnTaskFailed(ByVal taskHost As TaskHost) Implements IDTSEvents.OnTaskFailed
OnWarning(ByVal source As DtsObj
OnPreValidate
..
..
Thanks in advance,
View 4 Replies
View Related
Sep 25, 2006
Hi everyone,
I was wondering when events inside IDTSComponentEvents interface are called when you throw a SSIS package? I've got a private class which implements IDTSEvents and along with that got another one that implements the IDTSComponentEvents. I see how neither of them are fired when I debug the code by using F11, and I don't know what is it for.
Let me know your comments or come back to me if you need further details on that.
Thanks in advance,
View 4 Replies
View Related
Feb 27, 2008
Hi all - so I know how to raise a custom event or information message from a script. It this the only way it can be done. I'd like to throw an informative message when a package starts, grab that in the event handler and send out email message when the package starts and when it finishes. I would like to pass on some of the variables of the package so custom would be the choice. I know if I throw a scipt in there I can send oninformation, onerror, onwarning but can it be done any other way
thanks
View 5 Replies
View Related
Aug 16, 2007
Hi,
I have one problem when I try to update one record in the data base.
Such error arises when I update a table and a trigger raises an error, I cant catch it within the Row_Updating event command and I dont know how to get it within the Row_Updated event, because the exception happes before that command.
Also, I tried in the sqldatasource updating event but I got no results.
Any idea?
thanks in advance.
View 1 Replies
View Related
May 16, 2006
I am newish to databases and would appreciate some
advise. I think I have a solution to my
problem but it is going to take me a lot of time to get it running. If there is a better way of doing it I would
like to know.
I have a table :-
“eventDates� with
columns (id, date, eventID,
eventCount)
The id auto increments as a Primary Key.
date holds the date of the
event.
EventID references another
table with info about the events
Up to 9 eventIDs can be added
for each date and I want eventCount to hold an integer (1 to 9) to allow me to “pivot�
the data to the table below
“results� with columns (date,
eventCount1, eventCount2 …..eventCount9) so each row will hold a date and non
to nine eventIDs occurring on that date.
Is there an easy way to keep eventCount accurate or do I
just have to write a lot of code? I
will need to be able to remove events as well as add them. I will use a mixture of stored procedures
and VB.Net I guess?
Many thanks for any advice.
Mike
View 4 Replies
View Related
Jul 24, 2007
Hi Again,
I have now had the request to pull out more information but now lits all the days and the call that were logged.
I have tried to adjust the script I was given in this forum but I do not know enough to get it working.
It ultimatly does what I require but nw listing all the days in a month with a count of records.
This is the script I was given.
select month(DateRaised) as mth
, count(*) as mth_count "
from HD_Call
where [DateRaised] >= '2007-01-01'
AND [DateRaised] < '2008-01-01'
AND [Completed] IN(1, 0)
GROUP by month(DateRaised)
Is it possible to get
Day day_count
1 32
2 21
3 12
4 1
5 0
6 12
...
31 25
View 8 Replies
View Related
Aug 16, 2007
we are trying to simulate a disater recovery of our SQL2000 and SQL2005 development servers. Which is the order in which we should restore the DBs. I am talking specifically of MSDB, master and user DBs; should we restore user DBs before master and MSDB?
Thanks,
Carlos
View 5 Replies
View Related
Jan 23, 2008
Hi. I'm trying to calculate the total number of cases that more than 4 events. This is my query:
select count(defendanteventpros.vbkey) as countcase from defendanteventpros
where exists (select * from defendanteventpros where eventid=2 and eventdate between '1/1/2007' and '12/31/2007')
having count(defendanteventpros.vbkey)>4
The defendanteventpros table has a vbkey (primarykey) eventid (type of event) and an evetndate (when the event takes place).
What I'm trying to find is how many cases had 4 or more events and were received (eventid=2 from above) between 1/1/2007 and 12/31/2007.
I keep getting a big number that I know is not correct. I would appreciate any help anyone can offer! Thanks!
View 7 Replies
View Related
May 12, 2014
I have a table containing some events. Those events all have a start date and an end date.
CREATE TABLE #events
(
eventID int,
eventname char(30),
startdate date,
enddate date
[Code] ....
When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.
View 9 Replies
View Related
Oct 6, 2014
I've setup a deadlock monitor using extended events like this.
CREATE EVENT SESSION [deadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)
[Code] ....
Deadlock happened couple of days ago. I'm trying to determine the cause of deadlocks. What script should I use to pull that information to see what objects/processes caused deadlock?
View 8 Replies
View Related
Oct 12, 2015
I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end). Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL?
Code:
USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT *
FROM sys.server_event_sessions
WHERE name='TimedOutSQL')
[code]...
View 2 Replies
View Related
