getting trace of changes in database

Hello there. I've build database trigger who saves all schema changes in 
some table. and i use it to apply changes in another server.

It works fine as long as i do only shcma changes as part of schema and not 
another actions that relate to schema changes like: sp_rename, (Insert, 
update, delete) on table as part of schema change.

is there a way to add also these changes to the table who save schema 
changes automaticlly? 


0
Roy
9/12/2010 10:47:49 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
1193 Views

Similar Articles

[PageSpeed] 54

Roy Goldhammer (royg@yahoo.com) writes:
> Hello there. I've build database trigger who saves all schema changes in 
> some table. and i use it to apply changes in another server.
> 
> It works fine as long as i do only shcma changes as part of schema and not 
> another actions that relate to schema changes like: sp_rename, (Insert, 
> update, delete) on table as part of schema change.
> 
> is there a way to add also these changes to the table who save schema 
> changes automaticlly? 
 
If you have tables with predefined data, so that INSERT, UPDATE and DELETE
are logically the same as DLL, you could set up triggers on these tables. 
But you would need to find a way to avoid that these triggers are not 
installed on that other server. And since you cannot capture the very 
statement, you would need to store the changes in some other way.

When it comes to sp_rename, this is difficult in SQL 2005. Have a trace 
running to pick up sp_rename is a possibility, but I would not
consider it practical.

In SQL 2008 this is different, as sp_renamed are trapped by DDL triggers. 
(There are a still few system procedures that are not, but they are minor
issues. I think sp_autostats is the most important of these.)

A completely different option is to invest in a tool like SQL Compare
from Red Gate that compares two databases on schema level. They also
have SQL Data Compare to do the same on data.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/12/2010 12:01:05 PM
Reply:

Similar Artilces:

How do I retrieve changes to an unsaved excel document
I closed a excel document without saving changes. Is there a way to retrieve my old worksheet which would have the changes I made. I'm using excel 2000 which is part of my Microsoft Office 2000 program. No it's not possible -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Jim" <Jim@discussions.microsoft.com> wrote in message news:4FABAC1F-A402-4C87-BCDA-1124E072D9D3@microsoft.com... >I closed a excel document without saving changes. Is there a way to >retrieve > my ...

How do I build a database of names and addresses?
I'm new. Would like to build a database of names and addreses for a newsletter. Help Please. Thanks, Gary Hi maybe: http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Regards Frank Kabel Frankfurt, Germany "Masterguide" <Masterguide@discussions.microsoft.com> schrieb im Newsbeitrag news:4279A401-71C9-4307-AD33-856A7A273FBA@microsoft.com... > I'm new. Would like to build a database of names and addreses for a > newsletter. Help Please. > > Thanks, > > Gary Gary For help on Word mail merge using Excel as the data source. http://www.m...

Can't open database window using shift key...
Hi there, I have an Access database that I distribute to my client on CDROM. The start up options of the database are set so that the database window is not visible to the users. When the database is updgraded the database manager copies it from the CDROM to the relavant PC and then uses the shift key on opening in order to display the database window and link to the tables in the existing back-end. This procedure has been done many many times without a problem... BUT last week he found that holding down the shift key whilst opening the database no longer opened the database window and leave...

Unable to Get Statements
I am able to get statements on one windows xp screen name but not on the other. The working screen name works great asking which account to download to and everything, the other looks like its downloading but never imports the information. I have reinstalled ...

How to get more glue on a lifeline?
In a sequence diagram, I have an object with a long lifeline, more than 20 inches. It seems that at some point, those blue glue points on the lifeline no longer appear. This is a bug or a "feature" in Visio? Is there anyway to get an indefinite number of glue points. Did you try increasing the magnification or a screen refresh? It does help in some cases. Sandeep -- http://www.EventHelix.com/EventStudio EventStudio 2.0 - Generate Sequence Diagrams and Use Cases in PDF None of the is that problem. Basically, if I expand an item beyond about 20 inches, the blue glue marks just...

Change color for each serie?
Hi all, Is it possible to to change the color in a chart where 3 dataseries is represented? Eg. one color representing one region I have tried to create 3 series in the chart unsuccesfully. It seems that excel dosent allow it although I only have about +60 data in each serie... The data is also sorted (by size and not by region), so it is not possible just to select the data and assign them a specific value representing one color.. Hope that my question is clear. :) Christian ...

Backing up open database
Hi All, Two part question: can an open database be backed up (by the user who has it open) and if so, are there any issues associated with doing so? I have an Access 2000 database through which we crunch payroll data from a Progress database for the purpose of printing information not stored in the Progress database on check stubs. The Access database is not set up such that the users open it exclusively however, I have code in place that prevents a second user from opening the database if someone's already in. As part of the payroll processing, I have the payroll clerks notify me...

get the username of the logged on user
I am creating a windows service which is running under Localsystem account. How can i find out the username of the currently logged in user. GetUserName() function returns "SYSTEM". Even the GetTempPath() function returns the path of systems temp directory. And every access to HKEY_CURRENT_USER registry key gives me wrong information as the current user in such case is .Default Can anyone please tell me a way to find out the name of the logged in windows user??? The service has to run in system context so i cant change it. Thanks in Advance... "Rohit Kumar" <Roh...

How to get a programming job?
I know this is an awkward question but I am just stumped! I am presently a College student at Devry University Online. I am working towards my Bachelors in Computer Information Systems. I have a 4.0 GPA, Dean's List Award & President's List Award. I have already graduated from College America with my Associates Degree in Computer Programming, with similar achievements as those listed above and was a College America valedictorian. Yet none of that helps me because every programming job I run across on Dice & CareerBuilder require at least 1 year industry experience ...

How do I get my new messages to populate when they arrive?
My new email messages do not show up in my inbox until I go to another folder and then back to the inbox folder. Then they show up. issue #2 I get a copy of my reply email in my inbox. How do I stop this? Do you have a firewall between you and your server? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Lonnie" <Lonnie...

How do I get dates recognised as annual quarters?
I am using a spreadsheet as a KPI planner. I need to report quarterly. Each KPI has a day month and year assigned to it. I would like to automate this so that when a date is entered in a cell, the annual quarter in which that date falls automatically appears in the adjacent cell. Does anyone know how I can do this? If your company uses standard calendar year and regular 3-month quarters, try this: For a date in A1 This formula returns the quarter number: =CEILING(MONTH(A1)/3,1) Does that help? *********** Regards, Ron XL2002, WinXP "Rina" wrote: > I am using a spr...

Getting Compaq to Boot With 4 Gig Memory
We have a Compaq W8000 workstation with 4 GB of memory installed. Unfortunately Windows can only retrieve and use about 3.61 GB of that. It looks like the BIOS of various cards might be locking up part of the first 640 KB of memory? Can anyone give some advice on how to get Windows to get use of the full 4GB of memory? -- W On Mar 9, 7:40=A0pm, "W" <persistent...@spamarrest.com> wrote: > We have a Compaq W8000 workstation with 4 GB of memory installed. > Unfortunately Windows can only retrieve and use about 3.61 GB of that. = =A0 It > looks like...

How do I get around the MSVC++ Dialog Object Limit?
I am developing in MSVC++ .Net 2003, using MFC, building a realtime telemetry display. I have reached an object limit of 255, but need to add another 20+ objects. Is there a simple way to increase this limit? I am aware that I could possibly add more views or tabs, but that is not my first choice for this display as I would like all items visable at the same time. SpaceDust wrote: >I am developing in MSVC++ .Net 2003, using MFC, building a realtime telemetry >display. I have reached an object limit of 255, but need to add another 20+ >objects. Is there a simple way to increas...

Error Number: 0x800ccc0e when I try to get messages
Hello, I am a postmaster of our network and I am having some problems with Outlook 2000 and our pop3 server. The problem is that randomly, Outlook's users can not download their mail because they get the error number 0x800ccc0e when they try to send amd receive mail. We are using Microsoft Outlook 2000 SP-3 (9.0.0.6627) and our server is a Linux Debian running a 2.4.27 kernel. I have tried with ipop3d and in.qpopper as a pop3 daemons and with both I get the error. There is no firewall between them and this is the tcpdump capture from the server (our pop3 server runs at port 40110, bu...

Can't get rid of mailbox??
I have a user that was disabled, then removed from the domain, but I can't get rid of this _DIS <username> from showing in the Mailbox Resources screen. Any ideas? Thanks!! Do you have mailbox retention set up (30 days by default)? Is the mailbox showing up in Mailbox Resources with a Red 'X' on it? Is it if you run the clean up agent? If it is showing up with the red 'X' and you have mailbox retention set that is why it is still there. You can manually purge it by right clicking on it in Mailbox Resources and choosing 'Purge.' -- Hope that hel...

Any more effective method to get selected item in Listbox?
Hi, I have a question about the Userform.listbox I would like to make a userform, which has 2 listbox. The first one (listbox1) is showing the sheet name in workbook. Another (listbox2) is showing the range(cells(1,1),cells(1,1).end(xltoright).column) of select sheet name (selected at listbox1) my normal practice to get selected item in listbox is below. Any more effective method? _______________________________________ k = userform.lisbox1.ListCount j = 1 For i = 1 To k If userform.lisbox1.Selected(i - 1) = True Then ReDim Preserve Array(j) Array(j) = i...

Change font size based on value of a cell
If the value of A1>0, I need the font size in a merged cell to change from the default 10 to 16. It needs to return to the default size when A1 returns to a value of 0. Can someone help with this? Thanks. Michael Here's a little macro that will do it....... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Range("a1").Value = 0 Then Range("B1:E3").Font.Size = 10 Else Range("B1:E3").Font.Size = 16 End If Range("B1").Select End Sub Vaya con Dios, Chuck, CABGx3 "MichaelRLanier@gmail.com" wrote: > ...

Cannot seem to get views to display across entities
Using the ReadyServer. Created a view with Phone Calls as the source data I add column for the street1/2/3 of the regarding contact. The resulting view does not show the streets even though I know there is information populated. ...

Chart Value Changes in VBA
When you have say a bar chart and drag the bar downwards this will change the value in the reference source cell. Is there a way through VBA to identify the source and the cell changed by when dragging the bar. That is, when you drag the bar in the chart let me know the cell that ahs changed! Any help would be appreciated. Thanks in advance. Howard John, If your objective is to prevent the cell changes via the chart, you can protect the worksheet (Tools - Protection). The cells should be locked (Format - Cells - Protection). Or you can use the Chart_SeriesChange event macro for the cha...

Change font color for row
If A1 is numbered 1 thru 4, representing Black, blue, green and red, is there a way to change the font color for an entire row based on this? i.e. if I place a 2 in cell A1, I would like the entire row of numbers and statements to change to a blue font. Thanks in Advance Mac "Mac" <Mac@discussions.microsoft.com> wrote in message news:12170EBC-BF99-4759-9171-C8B319364690@microsoft.com... > If A1 is numbered 1 thru 4, representing Black, blue, green and red, is > there > a way to change the font color for an entire row based on this? i.e. if I > place a ...

how can I fix the size of frame? once I fixed ,the user cannot changed its size
handle WM_GETMINMAXINFO see http://www.codeguru.com/forum/showthread.php?t=318933 "msnews.microsoft.com" <on_lixing@163.com> д���ʼ� news:OgxZDM45FHA.472@TK2MSFTNGP15.phx.gbl... > > Handle CFrameWnd::OnGetMinMaxInfo and specify whatever size you want for your frame to be. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "msnews.microsoft.com" <on_lixing@163.com> wrote in message news:OgxZDM45FHA.472@TK2MSFTNGP15.phx.gbl... > > ...

Get rid of Double Space in Calendar Notes ??
When I cut and past test into calendar notes for an event the text is double spaced. I change the font but can never delete the blank line of the double space. How can this be cleared out ??? ...

How do I get the header to print out for e-mail messages?
For some reason, I am unable to print out the header of e-mail messages and it only prints out the message but not the time/date, subject, To..., From...., etc. Does anyone have an idea how to get this back on? Aloha yoda_the_webmaster, I'm guessing you're using Outlook 2003 (or earlier?) with Internet Explorer 7. That's a known issue that the IE team is working on. In the meantime you can either try and go back to IE6, upgrade to Outlook 2007 (which is unaffected because it doesn't use IE to print HTML messages) or edit the message and change the format to Rich T...

OMA Problem Getting Desperate & Running out of Ideas
Hi I am looking to roll out OMA through my corp, but can't seem to solve this prob I am having with logging on I have been through the available papers and there none out there which apply to this or work for it I enabled OMA on my local Exchange 2003 box which is running Server 2000, the OMA directory in IIS contains the correct mailboxes. However when I connect through I.E6 to http://servername:port/oma, I seem to sucessfully logon, but then get then get blocked with a HTTP 500 INTERNAL SERVER ERROR page cannot be displayed screen. I also get this when I browse OMA through IIS. Though I...

Change base language
Hi I need to change the base language of CRM 4.0 I think I have to uninstall CRM and delete the CRM databases and then install again in the other language, it's not a big problem because there is no data in CRM but what about the registration would there be any problems registering the same server again ? /Jack There will be no problem in registeration .Never seen it . ------ Aamir Blog = http://mscrmsupport.wordpress.com/ No it all went well, uninstalled and deleted the databases and a new registration with same information and we are up running the right base language :) /J...