Hello
The problem is need to find out the querry that has updated or inserted
into the table and in turn 'Triggered the Trigger'. I have the user
name, the machine name, Application name, but not the query. The update
is not desired and the application is doing it but the application
being so large we are unable to pin-point the code which is doing the
dammage.
Pls help!
Regards
Anubhav
Hello
If I have a trigger for INSERT and DELETE, how do I know what action was executed ??
I prefer not to split the trigger to one for each action, because the the procedure for both is almost the same.
Another question, How can I know what records were updated? I need to concatenate the ID/ID's to the SQL in the procedure.
Thanks,
Inon.
Hi,
I am developing desktop application with SQL Server 2005 in C#. I need your help. My Question is "How to notify a Database trigger is executed thru C# application?".
Now I am going to explain clearly. I have a table "Employee" and I created one trigger (it may be INSERT/UPDATE/DELETE) for that table. Then I created a C# application to show a msg box when that table is modified. Now my question is If that table is modified a message box will be shown. I know about trigger (If trigger assigned table is modified that trigger will automatically executed.) But how do i know that trigger is executed thru a C# application. Please send your suggestion or links.
Thanx,
GANESAN MURUGESAN.
My question is it possible to send a report developed in reporting services via email as either a attachment or imbedded in the email, from an insert trigger placed on a table in sql.
If so could you please help with the string that would be need to achieve this.
The report will need to have one or two parameters passed to it from the table.
The report is built and working through reporting services already. I was impressed with the subscription services that reporting services has in place and would like to utilise the reports further by auto emailing out when a new record is placed in the table. To be sent out either as an attachment or imbedded in the email it self.
Thanks for any assistance in advance
David
hi,
i want to be able to get the columns from my table except for its pk. the long method is to selecting all columns by listing them one by one right? but what if i got 100 columns..
so.. i wanted to use the information_schema..
i have:
use dbase;
select column_name
from information_schema.columns
where table_name = 'table1'
and column_name != 'pkid'
so there i'm listing the columns, but how am i suppose to select those columns from my real table?
thanks in advance!
Hi,I have a table with two columns Task and Employee. It lists all thetasks and the assigned employees. The same task will have multipleroles with an employeename or 'manager' as the data.If I have the following data'sales', 'john''sales', 'manager''dev', 'manager''make_coffee', 'manager''browse', 'jane''browse', 'manager'I need to get the rows wherever an employee is named (sales and browsefor example) and get manager for the rest.I can make it in two queries. Look for not manager in one and then formanager. Is there anyway to get them in a single query?If I need to look for 'sales', I need to get 'john' and not 'manager'.How to do that in a single query?Another need is to list all tasks with assigned. So for the above, Ishould get the following list'sales', 'john''dev', 'manager''make_coffee', 'manager''browse', 'jane'with two queries, I will get'sales', 'john''browse', 'jane''dev', 'manager''make_coffee', 'manager'which is ok. Order/sequence is not important.TIA,Sreelatha
View 2 Replies View RelatedTable has details like below:
NAME UPATE-TIMESTAMP
==== ===============
mary time1
mary time2
mary time3
tom time1
tom time2
tom time3
tom time4
richard time1
richard time2
Output Expected:
NAME UPATE-TIMESTAMP
==== ===============
mary time3
tom time4
richard time2
In summary, the requirement is to fetch the latest upated record for each unique NAME.
Having two tables.
1. ID Year
----------
1 1990
2 1991
3 1992
2. Name Year
--------------
ABC 1
XYZ 2
123 3
By passing year as an argument, Want to retrieve all the Names that does not have records for that particular year.
For example, if pass 1990, then the result set should be 'XYZ' and '123'
Thanks in advance
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>
Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);
I created a view with a simple join query
there are 2 rows with orderid as null, i am unable to fetch the details when i give WHERE Condition as
"select orderid from joins where orderid=NULL"
Query :
create view joins as
select A.customerid,A.Companyname,A.Contactname,A.City,B.OrderId from Customers A
left join orders B
on A.Customerid=B.Customerid
I've setup alerts and an operator to email on a particular event code.
I can successfully test the operator. (It e-mails fine)
The problem I am having is that despite having a particular event
log to the NT application log, no SQL Alert is triggered.
I've tried logging to the NT Application log, as well as the SQL errorlog.
The SQLExecutive is running.
Thanks in advance.
John
Hi guys,
Hope you are all well.
I am trying to set up a 'SQL Server event alert' that will 'net send' a warning message to one of the Operators that have been set-up on our SQL Server.
The alert has been configured to act on severity level '009 - User Defined', for all databases.
I have created a user defined system message with a severity level '9' on the sysmessages table.
EXEC sp_addmessage 80000, 9, 'My User Defined Message'
...and I have raised this error on our SQL Server.
RAISERROR(80000, 9, 1) WITH LOG
The error message is being generated in the SQL Server Log file, but for some reason my Alert does not appear to fire off the 'net send' event.
The operator is 'available to receive notifications', and I have tested the 'net send' command successfully.
Does anyone know why this Alert might not be sending a message with the 'net send' command?
Thanks in advance,
Kevin
I've been studying/experimenting with the replication features and though the Updating Susbcribers Replication Model was the answer to my data environment.
What we want: We have a live server (Publisher) that'll serve content to our users, but we want an "offline/data entry" server (Subscriber) that'll pretty much have the exact same data as the live server for data entry and validation purposes. Changes and inserts would be done on the Subscriber locally, vetted through, and once done, it'd be updated to the Publisher.
I've managed to get some semblance of the above working, but the updates are nearly instantaneous. Which means that changes/inserts that may be erroneous on the subscriber machine would almost always be seen on the publisher machine before our operations can do anything about it.
Is there a way to configure the replication process to hold off until the admin triggers it? Or am I using the wrong feature?
How do I make an event to fire for every 100 rows? I have set default buffermaxrows as 100, so I expect SSIS to write to the destination after every 100 rows and during that time I believe OnPipelineRowsSent would be fired.
But in my case, it write around 350+ rows and OnPipelineRowSent is fired only 3 times (350+350+350). Is there anything else I can do to fire this event for every 100 rows or its not possible?
Thanks
I am interested in creating a stored procedure that will trigger a script if an insertion to a particular table is done. I have never done this before, so I was wondering if someone could share any advice.
Thanks.
I would like to know how to config SQL Server 2000 Alert to be triggered by new file creation (i.e. txt file).
Maybe by monitoring windows 2000 log file (not sure)??
Any help or suggestion will be highly appreciated.
I have a child package which is executed several times within the same SSIS ETL. I have placed a break point on one of the child package's tasks, set to trigger on a PreExecute() event. The first time the child package is invoked, the breakpoint is triggered. However, on each successive invocation the breakpoint is ignored. Does anybody know if this behaviour is normal? Thanks in advance!
View 1 Replies View Relatedi have a package which logs to a table the status of its execution. i wonder if there is a way to trigger an event when package execution is terminated.
How is cross platform recognition triggered in the process?
View 2 Replies View RelatedHi,
I have a job in SQL Server agent with 199 SSIS steps and growing. In the job log only 99 steps are shown, which is a problem if a step higher than 99 fails to run. I need the steps to run in sequence and preferable would like to split the job into several jobs each containing no more than 99 steps. The only thing I don't know is if it is possible to have the next job in sequence start only when the previous job has finished.
Does anyone know if this is possible, and if so how it is done?
Many thanks,
Paul
I have a Send Mail task as part of a validation process in a OnPostExecute event handler. All tasks preceding the Send Mail task get triggered and show green check mark next to them, but, for some reason, Send Mail task never gets one.
I checked the Execution Results tab and it doesn't show any indication of Send Mail task executing as a final step.
I checked the SMTP server configuration and did some test using localhost. I'm able to send as configured while using anonymous credentials.
I have multiple task flows with precedence constraints and i have changed the constraint as completion to execute the package even though there is failure in one of the tasks in the package but every time the send mail success is executed instead of Send mail Failure task.
View 0 Replies View RelatedI 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 ?
Hi,
I have a table by the name EmployeeDetails which has Empid, Empname and Date of birth. The empid column is set to auto increment.
Now I want to create a trigger in such a way that, whenever a new employee is added to that table it will display the empid of the newly added emloyee. I don't know how to do that.
Please help?????
say i have a query like:
UPDATE table SET status = 1 WHERE id = 1000
if i have a trigger on that table, is there anyway i can get the id ?
i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?
thanks
I have a trigger written below to update another table when one table is updated.
Basically it works when i update row after row from enterprise manager However when i use a script to update 10 or 20 rows then this trigger does not work. Can any one offer any suggestions on where this issue could be ?
DECLARE @iCount INT
DECLARE @OldValue VARCHAR(255)
DECLARE @NEWVALUE VARCHAR(255)
DECLARE @EQNAME VARCHAR(255)
DECLARE @EQTYPE VARCHAR(255)
DECLARE @TEST VARCHAR(255)
SET @NEWVALUE = 'INIT'
IF (UPDATE(VALUE))
BEGIN
SELECT @OldValue = deleted.value from deleted
SELECT @NEWVALUE = inserted.VALUE,@EQNAME = inserted.EQ_NAME FROM inserted where inserted.attr = 'AreaCfg'
IF (@@ROWCOUNT =0)
BEGIN
RETURN
END
IF ((@NEWVALUE = '') OR (@NEWVALUE is NULL))
BEGIN
DELETE [TABLE-1] WHERE ENTITYID = @EQNAME
RETURN
END
IF ((@NEWVALUE != @OldValue))
BEGIN
SELECT @iCount = Count(*) FROM [TABLE-1] WHERE ENTITYID = @EQNAME
IF (@iCount = 0)
BEGIN
-- No records are available so insert
SELECT @EQTYPE = Typ FROM IP_EQ WHERE EQ_NAME = @EQNAME
INSERT INTO [TABLE-1] (AlarmGroupId, EntityID, EntityType, OwnerId) VALUES (@NEWVALUE,@EQNAME,@EQTYPE,'Production Tracker')
END
IF (@iCount > 0)
BEGIN
-- No records are available so insert
SELECT @EQTYPE = Typ FROM IP_EQ WHERE EQ_NAME = @EQNAME
UPDATE [TABLE-1] SET ALARMGROUPID = @NEWVALUE WHERE ENTITYID = @EQNAME
END
END
END
I need to trigger an email to user when a work order due date is approaching 1 day prior to the due date. Also I need to trigger when a user adds, delete, or modify work order to check on work order due.
Not sure how to query it in SQL.
Due Date = NeedByDate
user = AssignedTo
i have set up
a linked server. i can query the linked server in query analyzer and
also do update/delete. but when i try to run the same query for linked
server through insert trigger, i get following error: [OLE/DB provider returned message. [Microsoft][ODBC Sql Server Driver]Distributed transaction error].btw, i am using Sql server 2000, SP4. main server is windows 2003 server and linked server is windows xp pro.any suggestions will be appreciated.
Hey guys maybe you can help me out, been trying to figure this one out all day at work. I know how to use columns in a table to calculate another column in that same table. But I need to do some math on columns from a totally seperate table. Here is my scenario
table 1 = stock table
table 2 = Purchase order table
in table 2 there are line items that have ordered quantities for parts that we have ordered
in table 1 under each part number is a field for "quantity on order"
I need to compute the "quantity on order" in table 1 by summing all of the quantities in table 2 where the partnumber = the partnumber from table 1
quantity on order (table 1) = sum of all quantities (table 2) where the part numbers match
so for part number 516 i have this
table 2
poNumber partNumber quantity
1 516 1
2 516 12
3 516 4
table 1
partNumber inStock onOrder
516 0 17(this is what i am trying to figure out how to compute)
any help on this qould be appreciated. I would like the database to automatically do this itself if at all possible.
Hi:
In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.
then I try to run a query as follows:
if exists (select rfABC.* A
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L
on A.Address = L.address and
A.metro <> L.metro
begin
print ' ---- Yes metroID <> LAMetro, start job exec.... -----'
insert into tempCatchMetroIDGPRS
select rfABC.*, metro, getdate()
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro
update rfABC A
set A.metro = L.metro
from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro
end
else
begin
print ' ---- no metroID <> LAMetro, skip job exec.... -----'
end
------------------------------
this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...
thanks
David
I'm trying to update (increment)
Company.SumtotalLogons
from CompanyUsers.NumberOfLogons
where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
any ideas?
I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.
The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.
I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.
I have tested the trigger to do a simple task, and this works.
I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables
When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.
I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @intIMEmail varchar(300)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@fetch_status = 0
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO