Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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
Database Change Tracking Software
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 Replies !   View Related
Tracking Object Change Dates
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 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
FullText Change-tracking And Update-index Were Disabled
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 Replies !   View Related
SQL Login Password Change History
Hello.

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

Thanks.


View Replies !   View Related
Schema Change History Report
There is a report when you click servername, report andrunSCHEMA 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
How To Change A Column Name
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 Replies !   View Related
Change Column Length
Can you alter a column and make it longer without dropping the column

View Replies !   View Related
Change Value In A Identity Column
How can I change the value in a identity column? I cannot use update to change its value.

Thank you

View Replies !   View Related
Change Row To Column In A Table
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 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
How To Change Column Data As Row
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 Replies !   View Related
Change A Column Name In A View


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 Replies !   View Related
Change Column Names
i am trying to change columnnames 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 tablesacross 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
How To Change Forumula For Computed Column
hi,
create table mytable
(
id int,
num int,
comp as id/num
)

now i want to change column comp to formula id/100.
please help me out.


Raghu sunkara.

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
Iam trying tochange the value from "T"to "A"in trans_typecolumnbasedonthe valuein Transfer_of_Providercolumn..

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 todo that...but becuase of NULL valuein "Transfer_of_provider"column .. it keepgiving 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
How To Change Column Order In Tables?
I'm using VBE 2008 and in the edit table schema box it doesn't allow reordering the columns. Any solutions?

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
How To Change The Datatype Of A Column Without Dropping The Table?
Can anyone tell me how to change the datatype of a column.
Without dropping and recreating the table...
The table is empty...




THNK YOU VERY MUCH...

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
Newbie-Alter Table To Change Column Name


I am modifying a table and some of the columns are changing. The following syntax is resulting in errors. What am I doing wrong?


ALTER TABLE [EDB].[SQL_test]

ALTER COLUMN EDBR_Nb EDBR_Nm varchar(10)

View Replies !   View Related
Derived Column / Change Date Format


Hi,

Is there a way using a derived column transformation to change the format of the date eg:

The input flat file's date is in yyyy/mm/dd and i need to convert / change this to yyyy/dd/mm

Is this possible?

Thanks in advance,

Slash.

View Replies !   View Related
Derived Column Error:can't Change Datatype
I am trying to replace the value of a column in a derived column component, but it will not let me change the datatype.

It has decided that the column is a float, which is wrong.

How can I change it to the correct type?


View Replies !   View Related
How To Change Visibility Of A Column Group In A Matrix?
Hi,

how to change visibility of a column group in a matrix?

Thanks,

Igor

View Replies !   View Related
Change The Seed &&amp; 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

Copyright 2005-08 www.BigResource.com, All rights reserved