June 9, 2009 at 8:26 am
Hi All,
In my database I have a table that stores product inventories.
A trigger is attached to this table, and will be fired each time the table content is updated
In this trigger, sql will execute
EXEC('DBCC INPUTBUFFER(@@SPID);');
and save the information into a Log table
From the EventInfo, I can see which store procedure or sql statement was executed to update the inventory table.
However, in the Log table, I found several records where the store procedure (from the EventInfo) is not in the database.
Below is an example:
TESTDB.dbo.sp_upd_1D016C841EC740B5751CB6D696234064;1
I couldn't find the object in sysobjects table.
So now I am trying to figure out how/where/who executed the
store proc (I assume it is a system store proc :unsure:) in the example above.
Does anyone have idea how I can achieve this?
Thank you in advance 🙂
weirdlNet
June 9, 2009 at 9:28 pm
weirdlNet (6/9/2009)
So now I am trying to figure out how/where/who executed thestore proc (I assume it is a system store proc :unsure:) Does anyone have idea how I can achieve this?
Hi,
You need to audit/log information of the insert record, then
Use one table to capture the all information of the table inserts
Like
CREATE TABLE Reports_Audit (
SLno int IDENTITY(1,1),/*Total No of records*/
DBNAME varchar(20) DEFAULT(db_name()),/* where the SP runs*/
LOCATION varchar(20) ,/*IF needed to capture the location*/
REPORTID varchar(20) ,/* IF needed to capture report name*/
REPORTNAME varchar(50) ,/* IF needed to capture report id*/
USERID varchar(20) DEFAULT (suser_sname()),/*Who runs the SP*/
processid smallint DEFAULT(@@spid),/*IF needed to capture SPID*/
RUNDATE datetime DEFAULT(getdate()),/*Insert date*/
APPL_NAME varchar(15) DEFAULT (app_name())/*IF needed to capture Application*/
)
And use this table in the trigger of the main table
ARUN SAS
June 10, 2009 at 4:35 am
Hello Arun,
Thank you very much for your reply.
Will definitely use your advice in future 🙂
I found out when the update was triggered.
The inventory table is published for merge replication.
The
TESTDB.dbo.sp_upd_1D016C841EC740B5751CB6D696234064;1
actually tells that the table was updated during
a synchronization.
I didn't expect this because by right this table is published for the
replication to 'download-data only'. Any changes made in user's
device aren't supposed to be synched up to the server.
Well, in the publication's Properties, turns out that this table was not configured correctly.
The problem is solved after I changed the setting and rerun the
server Agent. 🙂
Best Regards
weirdlNet
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply