1

Evening,

I have started in an organisation where a huge SSIS solution involving large packages pushes data all over the place including between servers. The person who developed it has left and I've been left with the task of figuring it out and debugging it...

I've produced some quite extensive DFDs to map out the relationships between stored procedures, tables, views etc but there are some tables where I have found it impossible to determine what process inserted into it/updated it.

What I'd like to do is to create some fields on the tables I am concerned about with default values each time the table is written to.

  • Field1,
  • Field2,
  • etc...
  • 'DateTime Record Added'
  • 'Process that 1st Inserted the record',
  • 'DateTime Record Last updated'
  • 'Process that Last Updated the record'

Where the process values would be in the form 'Server.Database.schema.UspName'

I've looked through sysprocesses and searched online and I can't find a solution. Any ideas?

{Using SSMS 2014}

Thanks in advance,

SliderSteve
  • 165
  • 1
  • 1
  • 8
  • Beware - adding or modifying columns will potentially cause other processes to fail. If you see usage of "select *" or "insert table select" (i.e., not specifying the column list in an insert statement), bad things will happen. A server-side trace filtered for specific table names is an alternative. – SMor Aug 25 '17 at 17:38
  • Thanks SMor, I'm using a DEV environment to figure out the process. once I've managed to understand what it's doing we are going to completely rewrite it so as to simplify the process and I was hoping to add these columns in for future troubleshooting. – SliderSteve Aug 29 '17 at 11:09

1 Answers1

0

Take a look at the links below. It describes how you can get information about the calling instance via a trigger. How to get the stored procedure name from a trigger when an update operation was performed?

Egbert
  • 158
  • 4
  • 12
  • A trigger was suggested by a colleague but we were concerned about the performance drop. I think this would work for one-off error checking but I was hoping to be able to add code to each of the tables and leave it there. – SliderSteve Aug 29 '17 at 11:04
  • I did manage to get the following to work for a usp that first inserts the record but it doesn't work when a record is updated: (object_name(@@procid)) – SliderSteve Aug 29 '17 at 11:05