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.
Does anyone know how to create an eventhandler for a dataflow task specific events (OnPipelinePostEndOfRowset, OnPipelineRowsSent, etc.)? These events are available for logging via the standard logging infrastructure, but there seems to no eventhandler for them.
The reason I'm interested is that parsing information logged by these events using builtin log providers is not easy (eg., the number of rows sent gets burried somewhere in the message column (i'm using sql provider). I'd like to capture this information and record it cleanly in a custom ssis metadata database i'm building. Any ideas are welcome. Thanks.
Hi,I have a SqlDataSource and am trying to establish if it returns rows using the selected event e.AffectedRows statement. Unfortunately the selected event does not fire. I am using a stored procedure with 2 parameters:applicantID - Int32 - Parameter source from control label textissueStatus - Int16 - Parameter source 'None' default value = 1 When i test it during configuration it returns 1 row as expected. I am manually running the DataSource using ds.DataBind(); after a button click. Anyone know why its not firing the event?Thanks
Have written a dll that has a number of mthds to create, start, stop, list, etc... a filesystemwatcher. The dll attaches to sql fine. The dll creates it's own eventlog scope (which does showup in the event log browser dialog). So I do know it is being instantiated...
can start a watcher, can list all watchers running...in listing them i call directly down into a hidden class that houses the FileSysWat object. The listing queries some properties of the FileSysWatcher object directly so I can see if it is truely running or not (aka: EnableRaisingEvents property, path property, subdirs property, etc...)
The dll is currently configured to write to the event log, AND fire a stored procedure in ea. callback...This is not working!!! Am never getting the EventLog Record nor the SP to fire...not exceptions being thrown...NOTHING!!!!
When I open a filemgr window and go into the directory being monitored, perhaps I'll rename a file, the FileSysWatcher isn't firing the registered callback (aka: fsw.Created += new System.IO.FileSystemEventHandler(somemthdhere))
The property EnableRaisingEvents is true.
Perhaps something unusual about being inside of the SqlServer.exe process I'm unaware of!?
any ideas or help would be great!!! Tried lots of different code config's...but callback's are not being called!!!
Again...I've wired up all avail callbacks: Created, Renamed, Deleted, Error. Nothing is Firing!!!
Am not setting any NotifyFilter bits (flags) as the ctor default(s) are fine for my needs...
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.
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?
It is possible for a task to be executed multiple times in parallel. e.g. If a package is executed more than once using "Execute Package Task". I understand that in the future the ForEach container will have the ability to execute all its iterations in parallel as well.
This is a problem. In the eventhandlers we know the SourceID of the container raising the event so we can tie together events raised by the same container but this simply isn't possible if the events are running in parallel. An example of where we would want to do this is drop a record into a table when a container fires OnPreExecute and then update that record with the container duration time upon OnPostExecute. This does NOT work when tasks run in parallel.
The solution to this is very simple. As well as capturing System:ourceID, capture a new value, System::ExecutionID as well.
In the meantime, can anyone think of another way around this?
cheers
-Jamie
P.S.
I have raised this bug here: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK46904 and have asked for the provision of System::ExecutionID. If you think its a good idea then plese go ahead and vote for it.
[Microsoft follow-up]
This is still a valid request. Does it need opening on Connect?
[Yes, I know I keep posting this, and I'm sorry for that. But I want someone to confirm that this is a bug and not by design. I feel it is very very important]
All,
I'd like someone from MS to confirm something for me if possible.
I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.
Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.
-Jamie
If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip
It doesn't need any configuration because there are no connection managers. You can just run it standalone. Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure". Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.
I'd like someone from MS to confirm something for me if possible.
I've just come across a situation where a package that is restarted from a checkpoint fails to execute any of its eventhandlers if those eventhandlers are scoped to a parent container.
Is this a bug or by design? Either way I'm VERY worried about this because this is not in the least bit intuitive. This is the most dangerous bug that I have seen in RTM.
-Jamie
If you want a demo of this happening then download this package: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20060103Package1.zip
It doesn't need any configuration because there are no connection managers. You can just run it standalone. Run it first time and it will fail but you WILL see msgboxes popped up by the eventhandlers. Go into "Script Task 2" and make the return value "Success" rather than "Failure". Run it a second time. The package will complete successfully but NO msgboxes will pop up from the eventhandlers. They are not executing.
Reply here or email direct with any queries!
[Microsoft follow-up] This needs either fixing or explaining.
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.
I cannot get this event to fire. I am using TextBox9 to see if statements are processed and it never fills so it appears the Button4_Click never happens. Any ideas? Thank you, <asp:Button ID="Button4" runat="server" OnClick="Button4_Click" Text="Delete Submission" /> protected void Button4_Click(object sender, EventArgs e) { string CompanyDeleteID = TextBox10.Text; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("DeleteSubmission", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@C_ID", CompanyDeleteID); TextBox9.Text = "SP completed"; }
PROCEDURE dbo.DeleteSubmission @C_ID intASBEGIN DELETE FROM tblCompanyInfo_Submit WHERE C_ID = @C_ID DELETE FROM tblStoreStudioSubmit WHERE C_ID = @C_ID DELETE FROM tblContractorSubmit WHERE C_ID = @C_ID RETURNENDWHERE CD_ID = @C_IDRETURN
I have a datasource on my page, not connected to anything. In the selected event, I have the following:protected void InitializedDatasource_Selected(object sender, SqlDataSourceStatusEventArgs e) {int total = e.AffectedRows; lblInitialized.Text = total.ToString(); } Howevever, because it's not attached to a gridview or anything, it seems the selected event never fires. How would i get it to run on page load?
Does anyone here know of a way to execute a DTS package from an ASP that works? I have been using a variation on Microsoft's example that has yet to work.
I know that this code is getting a handle to both the package object and the task object because it recognizes if I have them spelled incorrectly and gives me an error.
On the other hand when everything is correct it simply claims to execute but nothing really happens... My screen will read Executing... Done.
The script goes something like this, any suggestions?:
Const DTSReposFlag_Default = 0 Const DTSReposFlag_UseTrustedConnection = 256 Dim oPackage, strResult Dim oPump Set oPackage=Server.CreateObject("DTS.Package") oPackage.LoadFromSQLServer "hoaorg10","","",DTSReposFlag_UseTrustedConnection ,"","","","DirCopy" set oPump = oPackage.Tasks("Copy Data from Results to [lhr2000].[dbo].[Households] Task").CustomTask oPump.SourceSQLStatement = "SELECT * FROM HOREFPA WHERE NEIGHBORHOOD = 'testnbhd'" oPackage.Execute Response.Write "Executing package...<BR>" For i = 1 To oPackage.Steps.Count If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then oPackage.Steps(i).GetExecutionErrorInfo lpErrorCode iStatus = False strErr = oPackage.Steps(i).Name + " in the " + oPackage.Description + " failed.<BR>" End If Response.Write strResult Next If iStatus = True Then strResult = oPackage.Description + " Successful<BR>" Response.Write strResult End If Response.Write "Done.<BR>"
Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE.....
My working environment exist of Win 2000 advance server w/service pack 1 and SQL 2000 Enterprise no service pack applied.
I created trigger on INSERT table "A" which have to insert record into table "B". The trigger fired when records inserted by insert statement from Query Analyzer. But the trigger doesn't s not fired if I moved records from text file into table "A" using DTS Import/Export Wizard.
OK, I'm at a loss..it must be staring me right in the face.
I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.
Any ideas?
CREATE TABLE [dbo].[PIF_MEP99] ( [PIFRecID] [int] NOT NULL , [MEPRecID] [int] NOT NULL , [PrimaryInd] [char] (1) NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED ( [PIFRecID], [MEPRecID] ) ON [PRIMARY] GO
INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd) SELECT 1,1,'Y' UNION ALL SELECT 2,1,'N' UNION ALL SELECT 3,2,'N' GO
CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99 FOR UPDATE, DELETE AS SET NOCOUNT ON
-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary
IF Exists ( SELECT * FROM inserted i INNER JOIN PIF_MEP99 p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y') BEGIN ROLLBACK TRAN RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP' END GO
SELECT * FROM PIF_MEP99 GO
SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i INNER JOIN PIF_MEP p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y' GO
BEGIN TRAN INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y' COMMIT TRAN GO
SELECT * FROM PIF_MEP99 GO
SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i INNER JOIN PIF_MEP p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y' GO
DROP TABLE PIF_MEP99 GO
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
I have a job that fires every 15 minutes. Job is working. I looked in here select * from msdb.dbo.sysmail_allitems records are in here...
I looked in here and select * from msdb.dbo.sysmail_log it says the database mail is 2008-06-24 14:46:10.997DatabaseMail process is started 2008-06-24 14:56:13.453DatabaseMail process is shutting down Which it seems to do periodically ....through out the log
What starts up the process - to go run the email alerts.
I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire.
I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired.
The foreach loop below runs fine and it sends emails as expected but the SP does not update the Companies table. Any thoughts? How do I cause the SP to execute? PROCEDURE newdawn.EmailSentDate @CID intAS UPDATE Companies SET Companies.LastEmailDate = (GetDate()) WHERE tblCompanies.C_ID = @CID RETURN foreach (GridViewRow row in GridView3.Rows) { pstrTo = row.Cells[2].Text; CEmail = row.Cells[2].Text; CName = row.Cells[3].Text; CID = row.Cells[1].Text; CState = TextBox1.Text; CCity = row.Cells[5].Text; CCat = row.Cells[4].Text; CCID = row.Cells[0].Text; SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("EmailSentDate", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CID", CCID); try { System.String mailServerName = "mail.domain.com"; REST of code sends email to email address found in each row ....it all works find but SP above does not fire.
Someone please help me with this. I'm trying to fire off an already created DTS package. This package is stored within SQL Server's -- underneith the Data Transformation Services / Local Packages section. HOW CAN I FIRE THIS OFF FROM A VB .NET APPLICATION I'm familiar with strored procedures and using them in vb.net so if somone could lead me down that road I would be very much appriciated. Thanks in advance everyone, RB
Hey guys... I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select. I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply. however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire. Do anyone on the forum know why this has happened?
Sub DeleteUserTicket(sender as Object, e as EventArgs) Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")" RunSQL(strSQL1) strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")" RunSQL(strSQL1) End Sub 'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size Sub RunSQL(strSQL) Dim objCmd As SqlCommand Dim sqlConn = New SqlConnection(strConn) objCmd = New SQLCommand(strSQL, sqlConn) objCmd.Connection.Open() 'Check that the rows can be deleted if not then return a error. Try
objCmd.ExecuteNonQuery() response.redirect("ticketsystemtest2.aspx") Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>" Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else
Message.InnerHtml = "ERROR: Could not update record, please " _ & "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number Message.Style("color") = "red" End If End Try objCmd.Connection.Close() sqlConn = nothing objcmd = nothing End Sub
I created an alert (18453) to audit successful logins. After I was done with the alert I made I edited the alert 18453 to disable event log logging and then deleted the alert. My problem is that the alert is deleted but it continues to log to the SQL Server error log and the windows application log. I have tried restarting the SQL Server Agent and even had the server rebooted over the weekend. Has anyone else had this problem? Where is this configured and how can I disable it?
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...
Is DTS capable of updating a SQL Server table without firing the trigger?
I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.
FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26) Carl
CREATE TRIGGER trig_SAWakeUp ON tsnro FOR INSERT, UPDATE AS DECLARE @snro_stus char(10) DECLARE @snp_sht_dtm as datetime SELECT @snro_stus = snro_stus FROM tsnro IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED') BEGIN SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19)) FROM tsnro
INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM ) VALUES (@snro_stus, @snp_sht_dtm) END ELSE BEGIN INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM ) VALUES (@snro_stus, @snp_sht_dtm) END
i have an issue with an insert trigger sometimes not firing.
here is the trigger
CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175] FOR INSERT AS
INSERT into tPABillToAddr ( chrJobNumber )
SELECT chrJobNumber FROM inserted
when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.
1) I have two tables , chat and country tables , chat table has 3 columns(chat_id,language,chat_info) and media table has 2 fields(chat_id ,country), chat table will store all countries data (India,china,taiwan)
2) chat_id,language will be populated by insert statement, and chat_info by update statement, i have a below after update trigger on chat table,which should verify country from media table and copy respective record from chat table to chat_country table (chat_in,chat_cn,chat_tw - these tables will have same structure as chat table)
3) this trigger is not firing for some of the records ,no clue or info for which it is missing some records.
create TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat] after update as BEGIN
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ
I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.
I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.
So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.
For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.
Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.
However the activated proc still did not fire.
I had to disable and re-enable activation for it to work.
What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?
And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?
