How To Track Changes Made To A Specific Table?
Nov 3, 1998
Hi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it
and it has a delete trigger on it but we want to track the changes to
the table and were wondering how to track specific changes to a user database?
We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used
SELECT * FROM SYSPROCESSES
and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and
Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
View 1 Replies
ADVERTISEMENT
Jul 13, 2015
I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
View 5 Replies
View Related
Aug 23, 2006
I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose.
History
guid (uniqueidentifier)
dateCreated(datetime)
lastDateUpdated(datetime)
changesMade(varchar(50)) ---- eg; dropdownlist
oldValue(varchar(50)) ----- compaq
newValue(varchar(50)) -----dell
updateBy(varchar(10)) <------ username of registered user
comNo(int) = foreign key for Computer table
History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.
View 17 Replies
View Related
Feb 26, 2008
Hello there,Does anyone know of a way to track changes to an SQL Server database so thatI can easily run those changes at a later date?That is, I want to make schema changes, and record those changes so that Ican execute them 6 months later on a copy of the orignal database.Thank you kindly for any ideas anyone may haveJohn
View 20 Replies
View Related
Aug 2, 2006
Hello
I am using SQL Server 2000 and How should I track a specific procedure and DMLs in it with profiler, I was trying to filer ObjecName=MySP but it seems it does not track only the SP. Any idea?
View 2 Replies
View Related
Aug 2, 2006
Hello
I am using SQL Server 2000 and How should I track a specific procedure and DMLs in it with profiler, I was trying to filer ObjecName=MySP but it seems it does not track only the SP. Any idea?
View 3 Replies
View Related
Apr 3, 2014
I need to know /observe for a period like 10 days in a row HOW MANY TIMES a specific Stored Proc is executed on the server. How can I do that? is there a way to precisely know a number of such calls via DMVs?
View 9 Replies
View Related
May 28, 2015
In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
View 6 Replies
View Related
Mar 4, 2008
hi..i need help on how to create user logs with a specific transaction he made. for example, he added new item in the database, the grid wud contain user id, date, time and transaction. help po.. tnx!
View 6 Replies
View Related
Oct 10, 2007
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
View 4 Replies
View Related
Nov 7, 2001
Hi
We need to keep track of all changes that are made to our tables.
The changes will be saved in a table that records:
- the table in which the change was made
- the name of the field that was changed
- the old data for the field
- the new data for the field etc..
I've seen a few examples that record the name of the table that was
modified but none that record done to the field level.
Can anybody give some guidance?
Thanks..
Wayne
View 1 Replies
View Related
Jan 4, 2006
Hi
anyone please help!
I have created the database driven web application with asp.net and sql server 2000. now I want keep track three operation(insert, updata and delete) that have been made on tables in a SQL Server 2000 database. what i did is:
1, create a audit table with columns: auditTable, actions, actionUser, actionTime
2, create three trigger(insert, update and delect respectivily) for every table
my problem is that i can not get right user name. I use form authentication and i stored user login information in the database. every time, no matter who is logining to the web application, the action user is always SA. I user user-name() function to get userName(actionUser).
Please anyone can help me to get current login user name, or tell the best way to track operations on a table.
Thanks
jili
View 9 Replies
View Related
May 19, 2006
My company would like to start keeping track of everytime a table, stored procedure, or function is changed.
What is the best way to do this?
View 4 Replies
View Related
Mar 26, 2008
I'm building an application that tracks courses and equivalent courses. For example, say I have course A which a user should get credit for if they have taken equivalent courses. So if a user has taken (course B or course C) AND (course D or course E) he should get credit for Course A. What's the best way to setup a SQL table to create these type of equivalent relationships?
View 9 Replies
View Related
Feb 6, 2006
Hi,
I am looking to track any changes made to any table within a db into a single audit table which will hold as fields: the table that has been updated/inserted, the field that was changed, its primary key, the old value and the new value specific to that field, and the date it was updated/inserted.
From what I have read, it does not look like this is possible with a trigger on table as it is not row specific and that I might have to control this from the business layer (vb.net). I am correct in this assumption, or is there a way of tracking specific data changes through triggers.
thanks
Welly
View 5 Replies
View Related
Nov 29, 2007
Working on a "social networking site" and would like to have something that records a member's interactions within the site.So for instance if a member uploads a photo, I would like to record it and then display it as "[Member] added photo" with a link to the photo. Also, if a friend is accepted as a friend "[Member1] is now friends with [Member2]" and also if a member posts a comment on the forums "[Member] posted message in [ForumName]" with link to post.This is very similar to Facebook's News Feeds and MySpace Friend Updates.The only way I can think of having this is having an Activity table with a field for each different Id I want to record and an associated list of tracked Actions:Activity:idmemberIdactionTypePhotoIdMember2IdForumIdThen there would be the ActionType tableactionIdNameThe biggest problem I see with this solution is that I would need to add a new field in the Activity table for each different type of activity I would like to track. This could get pretty big as I begin to track more and more activity items.Is there some way I can generalize this. - Andy
View 2 Replies
View Related
Apr 1, 2015
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.
View 8 Replies
View Related
Mar 22, 2006
Hello all, I was wondering. I am having trouble in my old age remembering to update my schema table when I make changes to the database. Is there a DB trigger or something that I could write to update this table for me when I edit or update a table structure, view, Trigger, or stored procedure?
Thank you in advance,
Jim
View 5 Replies
View Related
Sep 9, 2015
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
View 4 Replies
View Related
Apr 8, 2008
Hello,
Can anyone shed some light on why the following:
declare @str varchar(2000)
set @str = 'SELECT * INTO #TmpTable FROM FormHistory'
exec (@str)
SELECT * FROM #TmpTable
gives the following error:
Invalid object name '#TmpTable'.
This is a very cutdown version of what I am trying to achieve so it might not seem obvious why I am writing it into a string and using exec but in the real code I do need to do this. I have cut it right back to try to get to the bottom of why this doesn't work. I suspect the # in the string is causing the problems.
Thanks for any help
View 16 Replies
View Related
Mar 13, 2007
New to SQL Server Compact Edition and I am getting my feet wet with the following tutorial: http://msdn.microsoft.com/msdntv/episode.aspx?xml=episodes/en/20060831MobileRB/manifest.xml
I follow the tutorial exactly, but the data I edit through the Edit Dialog Form is not saved to the SQL Server table even though all of the edits appear in the datagrid. They never make it back into the table.
Any help would be appreciated.
Thanks!
View 7 Replies
View Related
Feb 22, 2006
I need to copy the following columns from my Employee table in my Performance DB to my Employee table in my VacationRequest DB: CompanyID, FacilityID, EmployeeID, FirstName, LastName, [Password] = 'nippert', Role = 'Employee' I tried the advice on this website but to no avail:http://www.w3schools.com/sql/sql_select_into.asp
View 1 Replies
View Related
May 8, 2008
I am trying to count the number of value "99" for each column in the whole table
to see how many "99" are there per column for the whole table...
and get the percentage per each column.. How can I do this for the whole table?
output should look like
Column A 117 10%
Column B 120 14%
-- etc..
View 2 Replies
View Related
Jun 14, 2004
I'm new to SQL Server.
I've an online Database on a server. I've my own user name like "AKR". I want to create every table under that user. But what happens is whenever I create a table online it goes under "DBO" user name. How to avoid that?
Thanks
View 1 Replies
View Related
May 8, 2008
I am trying to count the number of value "99" for each column in the whole table
to see how many "99" are there per column for the whole table...
and get the percentage per each column.. How can I do this?
output should look like
Column A 117 10%
Column B 120 14%
-- etc..
View 1 Replies
View Related
Aug 24, 2006
hi
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
View 3 Replies
View Related
May 23, 2015
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
View 4 Replies
View Related
Jun 30, 2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
View 2 Replies
View Related
Jan 23, 2007
Hi All,
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
Any suggestions is appreciated
Thanks A lot in advance
e,g :
http://localhost/reportserver?/testreports/employee sales&UserID='ABC'&LName=Lastname='victor'&rs:Command=Render
View 1 Replies
View Related
Aug 21, 2000
We are listing available SQL databases for selection in a VB list box, but need to be able to then elicit the tables in the database for another list. I am sure it is something simple, could someone help me out with this? Thanks!!
Laura
View 1 Replies
View Related
Feb 20, 2007
Hi i need to create this query. I have a table here named "Subject" from the database "Filteredtask". I want the output to show in this way:
Subject Quantity
Application Recieved count of application recieved
Process Application count of process application
Screen Application count of screen application
Initial Interview count of initial interview
Technical Exam and Interview count of technical exam and interview
I have used this code. My problem is that i put it in descending order to obtain this output, but if for example Screen application had more count it will ruin the hierarchy of the subject.
SELECT subject, COUNT(subject) AS Expr1
FROM dbo.FilteredTask
WHERE (subject = N'application received') OR
(subject = 'process application') OR
(subject = 'screen application') OR
(subject = N'initial interview') OR
(subject = 'profiles assessment') OR
(subject = 'technical exam and interview')
GROUP BY subject
ORDER BY expr1 DESC
__________________________________________________
Your future is made by the things you are presently doing.
Andrew
View 4 Replies
View Related
Apr 8, 2008
Hi
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId
Returns
TeamMemberId MonthId
1 14
6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName
Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO
The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.
Regards,
Stefan
View 6 Replies
View Related
Aug 9, 2007
Hello Reporting specialists ... (GregSQL, Jarret, Lisa .....)
How can i get the max value of a single specific table column ??
Is this possible ? Or am i asking the impossible ??
Hopelessly, desperatelly remaining ...
Vinnie
see also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1979307&SiteID=1
View 1 Replies
View Related
