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
ADVERTISEMENT
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 ?
Or
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,
HHA
View 2 Replies
View Related
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.
Thanks,
JAndrews
View 4 Replies
View Related
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
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
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
Dec 30, 2014
How to reset the value of the change tracking anchor id?
CHANGE_TRACKING_CURRENT_VERSION()
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
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
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
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
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
ALTER
DATABASE [db_name] SET
CHANGE_TRACKING =
ON (CHANGE_RETENTION
= 2 DAYS,
AUTO_CLEANUP =
ON)
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
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
Jul 30, 2015
In a change tracking enabled database I can find the latest change tracking version number by using
Select CHANGE_TRACKING_CURRENT_VERSION() As Latest ChangeTrackingID.
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
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
Oct 4, 2007
Hello.
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?
Thanks.
View 1 Replies
View Related
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?
Thanks,
Peter
View 3 Replies
View Related
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
varchar(25);
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
Jun 30, 2015
How can I easily identify who dropped a table?
View 8 Replies
View Related
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
Feb 23, 2006
Hello,
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
sqlnovice123
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
Jun 4, 2015
How to change column size dynamically depends on content of the column.
View 2 Replies
View Related
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
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
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
Tony
View 1 Replies
View Related
Dec 20, 2004
Can you alter a column and make it longer without dropping the column
View 3 Replies
View Related
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
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
Jul 1, 2006
Hi
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.
--------------------
Initializing
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?
Imtaar
View 1 Replies
View Related
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
Apr 24, 2008
Hello,
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?
Thanks
View 1 Replies
View Related
Oct 28, 2007
hi
I have a table like this
Username
Clicks
Impressions
Download
User1
12
23
43
User2
34
33
59
User3
39
88
77
User4
34
43
43
need to change the table like this
User1
User2
User3
User4
clicks
12
42
43
43
Impressions
43
43
43
43
download
43
43
43
43
Thanks
Ezhil
View 4 Replies
View Related
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?
Thanks
Micror
View 6 Replies
View Related
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