Tracking Change History, By Column...
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Tracking The History Of Changes.
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 Replies !
View Related
Change Tracking For SQL Server
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 Replies !
View Related
Tracking History Using &"Effective Dating&"
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 Replies !
View Related
Problem With Full Text Search - Change Tracking
I'm running Sql Server 2000 sp4 with full text catalog on a Windows 2003 server. When testing with a restored large customer database, we've noticed that the change tracking isn't working properly. We have a tool that reads from some text files and adds/updates records in the database. After this tool is run, I see rows in the sysfulltextnotify table. However, they just sit there and are never cleaned out unless I rebuild the catalog. Once I do that, I get results when running a search. If I don't rebuild the catalog, I get no results when searching on the new data. Now, when I use a much smaller database, change tracking works just fine. Any ideas? Some researching suggested that I should have at least 15% free disk space on the drive where the FT Catalog files reside, which I definitely do not have. If it's a space issue, will I see errors in the event log? I currently do not see any errors there. Any other ideas about things I can check?
View Replies !
View Related
Schema Change History Report
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 Replies !
View Related
History/Data Change File Approach
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 Replies !
View Related
How Do I Get The Latest Column Value From History?
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 Replies !
View Related
Change An Int Column To Identity Column
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 Replies !
View Related
Change Column Name Dynamically
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 Replies !
View Related
Change Column Size
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 Replies !
View Related
Change Column Names
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 Replies !
View Related
SQL Change Column Data Type
How do I programatically change a column (say username) in a table (say tblusers) from varchar(25) to varchar(100)I am looking for something likealter tblusers set username as varchar(100) I know the above statement in nonsensical but it conveys the idea.
View Replies !
View Related
Change Capitalization In Sql Table Column
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 Replies !
View Related
TSQL Change The Position Of A Column?
We have a little app to help us move applications from developent --> stage --> production. It uploads the files, runs the new stored procedures etc. One of the things it does is create new columns in existing tables. In EM you can create a new column in any position, but ALTER TABLE seems to only add columns to the end of the table. Is there a way in TSQL to create columns in arbitrary positions in the table? Or do I have to recreate the whole table?
View Replies !
View Related
Column Update Structure Change
hiI want to increase a varchar(5000)to varchar(8000) on a table thathas approximately million rows.....What is the impact on the serveror any good recomendations of a action to accomplish this in the best andfastest way.thanks davep
View Replies !
View Related
Change Results In Column To Display Different
I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment. Here goes.. SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE FROM SAMPLE SAMPLE, SDIDATAITEM SDI WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND (SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') ) ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC
View Replies !
View Related
Change Table Column Width
I know we could change the data type or width of a table column in SQl 7 database, but I am not certain if this column is a primary key. Can we change a primary key column width? If we can, how about this column as a foreign key in another table? Will it be automatically updated to the same width? Thanks in advance for your input.
View Replies !
View Related
Change Column Data In All Tables At Once
I would like to change column data in all tables in a single database at once. For instance, i have many tables in a database, and about 30% of the tables has the column "orderplace". And all the data in orderplace (every tables in the database) are "houston". instead of going to each table and "update table set orderplace = 'dallas' " .... is there anyway that i could run a sql statement and it will update all the tables in database that has the orderplace column to "orderplace = 'dallas' " ? any ideas ? thanks.
View Replies !
View Related
Identify Change Points In A Column
DECLARE @EffLevels TABLE (ChangePoint int, Value Int) INSERT@EffLevels SELECT'1000', '767' UNION ALL--Changed SELECT'1000', '675' UNION ALL SELECT'1001', '600' UNION ALL--Changed SELECT'1001', '545' UNION ALL SELECT'1001', '765' UNION ALL SELECT'1000', '673' UNION ALL--Changed SELECT'1002', '343' UNION ALL--Changed SELECT'1002', '413' UNION ALL SELECT'1002', '334' UNION ALL SELECT'1001', '823'--Changed -- My Result should be -- ChangePointPrevChangePointValue -- 1000Null767 -- 1001 1000 675 -- 1000 1001 765 -- 1002 1000 343 -- 1001 1002 823 Any suggestion ?
View Replies !
View Related
Change Ordinal Position Of The Column
1 have a table with 74 colun.But due to some deletion i have the ordinal postions as 1 2 3 . . 69 70 72 73 74 76 what i wants is to change the ordinal from 72 to 71,73 to 72,74 to 73,76 to 74 SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName -- handle insert case here SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field SELECT @bit = (@field - 1 )% 8 + 1 SELECT @bit = power(2,@bit - 1) SELECT @char = ((@field - 1) / 8) + 1 --IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D') IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data. I needed it as i have a trigger whihc is adding value in a log table on update but due to this problem it fails for certain column Trgger is solme thing like WHILE @field < @maxfield BEGIN SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field SELECT @bit = (@field - 1 )% 8 + 1 SELECT @bit = power(2,@bit - 1) SELECT @char = ((@field - 1) / 8) + 1 --IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D') IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data. BEGIN IF @Type not in ('I') BEGIN SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION = @field -1 --print('fieldname = '+@fieldname) SELECT @sql = 'insert Audit (ActionTypeID, RowID, TableName, PK, FieldName, OldValue, NewValue, UpdateDate,UserID, UserName, UserType)' SELECT @sql = @sql + ' select convert(bigint,' + @ActionTypeID + ')' SELECT @sql = @sql + ',' + @RowID SELECT @sql = @sql + ',''' + @TableName + '''' SELECT @sql = @sql + ',' + @PKSelect SELECT @sql = @sql + ',''' + @fieldname + '''' SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')' SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')' SELECT @sql = @sql + ',''' + @UpdateDate + '''' SELECT @sql = @sql + ',' + @UserID SELECT @sql = @sql + ',''' + @UserName + '''' SELECT @sql = @sql + ',''' + @UserType + '''' SELECT @sql = @sql + ' from #ins i full outer join #del d' SELECT @sql = @sql + @PKCols SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname SELECT @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)' SELECT @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)' --print('@sq=====sdfdfsfsdf')--sha --print(@sql)--sha EXEC (@sql) END END END Kamran Shahid Sr. Software Engineer(MCSD.Net) www.netprosys.com
View Replies !
View Related
Change Value Using A Derived Column Transformation
I am trying to change the value from "T" to "A" in trans_type column based on the value in Transfer_of_Provider column.. I like to change "T" to "A" if the value of Transfer_of_provider column is "Y". "Transfer_of_provider" coulmn has NULL value.. I've used the below logic to do that... but becuase of NULL value in "Transfer_of_provider" column .. it keep giving me a truncation error.. TRans_type = (ISNULL(Transfer_Of_Provider) ? "N" : Transfer_Of_Provider) == "Y" ? "'T" : "A" How can I make this work?
View Replies !
View Related
Sql Script To Change Type Of Column With Data
I have a table say Friend which has a column "IsSingle VARCHAR(10)" and this column has values like yes or No Now I want to change type of column IsSingle from VARCHAR to BIT, if I try to do it manaually SQL throws error that cannot convert yes to bit etc. I know that we can write a script to do this task but i dont know how ? Any pointers,links, suggestions will help me to start with. Thanks for your help.
View Replies !
View Related
Alter Table - Change Column Datatype
Hi,I want to change the datatype of an existing column from char tovarbinary. When I run the "Alter Table" statement, I get thefollowing error message -Disallowed implicit conversion from data type char to data typevarbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERTfunction to run this query.Can the CONVERT function be used as part of an alter table/altercolumn? Is there another way besides renaming the table and creatinga new one?Thanks,Bruce
View Replies !
View Related
Change Increment Value For Existing Identity Column
Hi, How to Change Increment Value for existing Identity Column (MS SQL2000) ? I know how to change the seed : DBCC CHECKIDENT (activity, RESEED,4233596) but I need the future id generated with step 2 4233596 4233598 4233600 I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server . Thanks, Natalia
View Replies !
View Related
Adding A Single Change To All Rows In A Column
We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?
View Replies !
View Related
DDL Script To Change Column Data Type
I need a DDL script to change a column from an integer to money data type. I thought I could just use:- alter table dbo.packagefeature alter column site_fees money go However this fails because there is default constraint on the column with message:- ALTER TABLE ALTER COLUMN site_fees failed because DEFAULT CONSTRAINT DF_packagefeature_site_fees accesses this column. I can drop the constraint but I don't what its called on the target database. How can I find this in the script or is there a better way of acheiving this. Any help appreciated.
View Replies !
View Related
[Resolved] Change Column Value At Time Of Insert
Prior to insert (into #JobListTable) I need to change the value of the 't2.plant_id' column (from #EquentialJobListTable). This column will have a value of 110, 300 or 320. If 110 then value should be SBGB2, if 300 then values should be RGWP and if 320 value should be RGWP. Is this possible to do? INSERT#JobListTable ( job_date, job_number, job_phase, qty_received, plant_id ) SELECTt2.job_date, t2.job_number, t2.job_phase, t2.qty_received, t2.plant_id FROM#EquentialJobListTable AS t2 LEFT JOIN#JobListTable AS t1 ON t1.job_date = t2.job_date AND t1.job_number = t2.job_number AND t1.job_phase = t2.job_phase WHEREt1.job_date IS NULL
View Replies !
View Related
Change The Seed && Increment Value Of An Identity Column.
Dear Sir, the following code is copied from the SQL Server Help Example. CREATE TABLE MyCustomers2 (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50)) INSERT INTO MyCustomers2 (CompanyName) VALUES ('A. Datum Corporation') ALTER TABLE MyCustomers2 ALTER COLUMN CustId IDENTITY (200, 2) It gives the following error. Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'IDENTITY' Can U please guide me abt the error. with regards, wilfi
View Replies !
View Related
|
TRACKER
