Tracking Change History, By Column...

Jun 17, 2004

This one is giving me quite a bit more difficulty then I ever imagined it would...

Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.

I can't seem to find a good way to do this.

View 9 Replies


Tracking The History Of Changes.

Sep 11, 2006

Hi all,

I have several transaction tables on which I need to track the changes. That is I need to maitain
the history of changes. Only few column values are changed often.

Which is the best way for tracking the changes.

1.Store the whole record after the change ?


2.Store the ColumnName & its respective old & new value ?

Or any other better.

Note : UI part & SP's will take care of the tracking & no plans for triggers.

Thanks in advance,


View 2 Replies View Related

Tracking History Using Effective Dating

Apr 4, 2006

Can anyone provide me with some insight on tracking the history of my data using "Effective Dating"? I would like to keep track of an infinite history for many of the tables in my database and am trying to avoid duplicating data structures if possible. 
Any advice on this topic would be appreciated, I am most interested in any links to tutorials or support articles on subject that you may know about.  I have searched the web and the forums section and have not come across much.

View 4 Replies View Related

Transact SQL :: Implementing CDC For History Tracking

Mar 1, 2012

This question is based on both T-SQL as well as SSIS. We have around 120 history tables which would be loaded with the history records from the Main tables. Following is an example of a Main table and its corresponding history table

Main table - Patient
Columns - PatientID (Identity), PatientFirstName, PatientLastName, Suffix, DOB, etc...
History table - PatientHistory
Columns - PatientHistoryID(IDentity), PatientID , PatientFirstName, PatientLastName, Suffix, DOB, etc...

So almost all of the History tables would be having the same columns from the Main table along with the identity field for the history table.

At present we have After triggers for Update which pulls the records from the deleted table and inserts these records into to history table. I have been asked to implement it without triggers.

My question is - Can we implement CDC for this and is it the best option performance wise as well

View 4 Replies View Related

SQL 2012 :: Change Minimum Permissions To Allow Read Access To Change Tracking Functions

May 12, 2015

Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data

View 1 Replies View Related

Change Tracking For SQL Server

Jul 20, 2005

Has anyone come across a change tracking tool for SQL Server. Specificallythe scenario I want is the following :A Production DB needs some modifications to its content.This tool will copy the DB to a dev environment.The Dev environment will be 2 copies of the DB, 1 as a control set and theother the change DB.The developer makes their changes in the Change DB. They test them out andthen when they decide the changes are ready to pushto production they hitthe "go" button on the tool.The tool calculates the delta between the Control and the Change DB and thenpushes the changes to producitonAny ideasThankss

View 2 Replies View Related

SQL 2012 :: Change Tracking Anchor ID

Dec 30, 2014

How to reset the value of the change tracking anchor id?


It's a bigint that increments on every DML operation and I just wondered how you could reset it back to zero. Turning change tracking off and on doesn't seem to do it.

View 2 Replies View Related

Tracking Object Change Dates

Oct 7, 2005

Is there a table where last DDL change date exists? For instance, if I have added a column to a table is there anywhere I can find when this occurred? Auditors are askng about this.

View 7 Replies View Related

Database Change Tracking Software

Jul 20, 2005

Does anyone know of software that tracks changes to a database? Forexample, it would track anytime an SP or view was recompiled, or ifyou added or deleted a column to a database?

View 1 Replies View Related

SQL Server 2008 :: Change Tracking On Table?

Jun 8, 2015

best way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.

So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.

For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.

The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):

CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,

[Code] .....

method to track changes to this table with it being truncated every day?

View 1 Replies View Related

DB Engine :: Risk Of Enabling Change Tracking

Oct 6, 2015

We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an

DATABASE [db_name] SET
= 2 DAYS,

We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?

View 5 Replies View Related

FullText Change-tracking And Update-index Were Disabled

Jul 30, 2007

How can i enable my fulltex change-tracking and update-index in my table?
I recreated my fulltext catalog and start the full population, but although my fulltext index status shows active, my full-text change-tracking and the update index were disabled. - and I don't know how to enable them.
Thanks in advance

View 3 Replies View Related

DB Engine :: How To Find Last Updated Change Tracking Time

Jul 30, 2015

In a change tracking enabled database I can find the latest change tracking version number by using


Which will give latest change tracking id (example 1022), Is there a way to find the datetime of this latest change tracking id.

View 3 Replies View Related

Schema Change History Report

Feb 1, 2008

There is a report when you click servername, report and run SCHEMA CHANGE HISTORY
I had my SQL 2005 running for a few weeks and this is many listed from day started is there a way to recycle this and clean it up on a weekly basic

View 5 Replies View Related

SQL Login Password Change History

Oct 4, 2007


Is it possible to find out a complete history of when the passwords for any SQL Server logins were changed and by what/whom in 2005 standard edition?


View 1 Replies View Related

History/Data Change File Approach

Mar 4, 2005

I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.

The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.

I figured I'd add the needed code to the stored procedure that's doing the update for the record.

When the stored procedure is called to do the update, the PK and parameters are sent.

The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:

Name, DateTime, Changes

FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204

How does the above approach sound?

Is there a better way you'd suggest?

Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?



View 3 Replies View Related

DB Engine :: Stored Procedure Change History Information?

Jun 5, 2015

one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.

DECLARE @Parameter_XYZ

Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.

Can we get stored procedure text between different alteration.

View 4 Replies View Related

SQL Server Admin 2014 :: Schema Change History - Identify Who Dropped A Table?

Jun 30, 2015

How can I easily identify who dropped a table?

View 8 Replies View Related

DB Engine :: How To Convert Unique Clustered Index Into Clustered Primary Key To Use With Change Tracking

Sep 4, 2015

We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.

View 4 Replies View Related

How Do I Get The Latest Column Value From History?

Feb 23, 2006


I am using SQL 2000. I have the foll commad and the result set is as follows. I need to get data for 2/22/06 rather than the other dates.

Thanks in advance

select * from dbArchive..EMSecurityModelValSaveD where ModelID = 1 AND SecurityID = 60952

200602171NULL23559455660952-1.8737706198803314E-22006-02-16 00:00:00.0002006-02-17 04:34:07.213
200602201NULL23559455660952-1.8824870098869399E-22006-02-17 00:00:00.0002006-02-18 04:42:49.737
200602211NULL23559455660952-1.8973066282962407E-22006-02-20 00:00:00.0002006-02-21 03:10:07.203
200602221NULL23559455660952-1.9135545221188051E-22006-02-21 00:00:00.0002006-02-22 04:25:07.217

View 12 Replies View Related

Reporting Services :: Change Column Size Dynamically Depends On Content Of The Column?

Jun 4, 2015

How to change column size dynamically depends on content of the column.

View 2 Replies View Related

Change An Int Column To Identity Column

Dec 17, 2001

Hi, I want to change an int column (not null) to identity column. I tried
the following:

alter table myTable alter column ID int identity(10, 1) not null

But it failed with the error message:

Incorrect syntax near the keyword 'identity'.

Can someone please show me the correct statement (if it exists)>

Many thanks.

View 4 Replies View Related

How To Change A Column Name

Jan 9, 2008

I have created a table and found that i miss named a column. All i want to do is change the column name. But I don't see anything in ALTER TABLE to do that.

How do I simply change a column name?

View 2 Replies View Related

Change Row To Column In A Table

Oct 19, 2001

Hi, guru,

How can I Change all rows into columns from one table? what method can make it easy?

Thank you very much


View 1 Replies View Related

Change Column Length

Dec 20, 2004

Can you alter a column and make it longer without dropping the column

View 3 Replies View Related

Change Value In A Identity Column

Mar 9, 2004

How can I change the value in a identity column? I cannot use update to change its value.

Thank you

View 11 Replies View Related

Change Row Value As Column Header

Dec 22, 2014

I have two tables namely lu_parameter and tbl_param_values. The lu_parameter table consists of param_id and parameter column containing id numbers for the parameter names.

The tbl_param_values table consists of values corresponding to the parameters with the param_id value as the column header.

I want to join these two tables so that in the result query instead of param_id value as column heading, I need to have the parameter value as the column heading.

I have attached the screenshot of the data for reference. I am not that much aware of sql queries.

[URL] .....

View 1 Replies View Related

Change Column Size

Jul 1, 2006


I am using one Publisher with many subscribers for Merge replication of a database.
I wanted to change size one column in a table. So I added a dummy column (through Filter column).
Copied data, dropped old column (through Filter column), added new column, copied data, and dropped dummy column.
On subscriber A, replication is OK. On subscriber B, I get the following message.
Connecting to Publisher 'REPLIC'
Retrieving publication information
Retrieving subscription information
A column was added to or dropped from the replicated table.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
A column was added to or dropped from the replicated table.

I made a new snapshot, reintialized subscription with upload, and tried to sychronize again.
But same message.

Question 1. Is there anyway to make replication?
Question 2. If not, can I at least upload data changes before dropping and making new publication?


View 1 Replies View Related

Change Column Names

Jan 31, 2006

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

View 8 Replies View Related

Change A Column Name In A View

Apr 24, 2008

I am going to change a column name YYZ in a VIEW because of typo. It should be named as YYY.
What is best scenario?


View 1 Replies View Related

How To Change Column Data As Row

Oct 28, 2007


I have a table like this




















need to change the table like this




















View 4 Replies View Related

Change Column Name Dynamically

Nov 30, 2007

Hi All,
I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example:
Source table column Target table column

Name FN_Name
Age FN_Age

The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.

Which tasks or approaches can be used to implement this?


View 6 Replies View Related

Change Capitalization In Sql Table Column

Apr 10, 2007

I have some data that was entered in the DB in ALL CAPS. I would like to reformat all the rows so that it follows the normal "Capitalization". What is the best way to do this? Shoud I create a console application to accomplish this or is there some way to do it natively in sql server 2005.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved