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
1107 Views

Similar Articles

[PageSpeed] 25

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:

Database hang
I have automated my database to run during non peak times when no one is around and so that the Current data will be available when they come in the next morning. The problem I'm having is that when the database opens at the scheduled time (Windows scheduler) it launches creates the links and then hangs there until I come in in the morning and log on to my computer. Windows Scheduler allows me to store my user ID and password, so this should not be an issue. Once I log in the database continues without any input on my behalf. This whole delay is causing problem as the d...

how do I get into my bios (XP)?
I need to format my Laptop - so I need to change the boot order in bios. I don't know how to get into my bios ? Help During booting hit the Del key several times. This is how with an Award BIOS. -- Don Vancouver, USA "thicko tony" <thicko tony@discussions.microsoft.com> wrote in message news:9C54ADD9-0119-4A3F-A92F-E7B752621D32@microsoft.com... >I need to format my Laptop - so I need to change the boot order in bios. I > don't know how to get into my bios ? > > Help Dear Thicko, What scares me is that you want to update your BIOS so you post yo...

Any changes in CAdapt class from ATL 6.0 to ATL 7.0?
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C377BD.65849D60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable This used to compile fine in VC 6.0. Now I get this error -=20 error C2664: 'ATL::CAdapt<T>::CAdapt(const T &)' : cannot convert = parameter 1 from 'ATL::CComPtr<T>' to 'const IXPtr &' Thanks in advance. ------=_NextPart_000_0008_01C377BD.65849D60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DO...

JOIN FREE and Get a 2009 RANGE ROVER
As one of the Nets TOP Millionaires I never do free opportunities. A good friend of mine and fellow top earner showed me a check from this free opportunity that was so big it paid for a 2008 Range Rover and his 6000 Sq Ft House. I was shocked. I said you kidding right? He Wasn't. He just threw up banners on safelists and ad sites. I had to try it since it was free.I did and got a check for 10 Grand! Listen to this audio: http://www.byoaudio.com/play/WpmTgFBk Go to my website and lock your position in now! http://www.watchthemoneyonline.com ...

Fill Series only changing one input
I am not sure if this is possible, but I am hoping that it is. Right now, I am trying to create a formula (and fill down the column) that calculates the percentage of a total. My formula right now is as follows: =C2/(SUM('2010 Consultation'!B12+'2010_Raw Pivot'!C:C)) Column C in the sheet titled (2010_Raw Pivot) contains revenue. I need to know the percentage of revenue out of the total. However, the total is not just the summation of all of Column C. I need to take into account a cell that I have in another sheet (2010 Consultation! - Cell B12). When I use...

How do I get excel to create an email using Outlook?
I am using Excel 2003 and need to get the spreadsheet to generate an email using Outlook when a cell reaches a specific value i.e. date to several different email addresses. ...

change default language in spell check
I have recently purchased an Acer Aspire One netbook which came preinstalled with outlook express. When I try to send an email the spell check assumes I am writing in French and when I go to the tools/options/spelling feature, the only language I can choose is French - there is no other option in the drop down box. Has anyone had a similar problem/know of a solution. Outlook Express is a different product from Outlook. Post your query in microsoft.public.outlookexpress.general -- TedMi "8892mg" wrote: > I have recently purchased an Acer Aspire One netbook which came pr...

Group mail get stuck in local delievery queue
I have a company distribution group for all user in the company, every time when I send mail through this alias, it could only sent out the in order the user last name from Z-K, then the rest of the members in the alias (K0A) will get queue in the local delivery and it will never successful sent out and finally receive NDR. The message from the NDR show as " Could not deliver the message in the time limit specified. Please retry or contact your administrator.......#4.47" please help! Have you tried nesting the groups? "Chris Yeung" <Chris Yeung@discussions.mic...

get information in cell
Hi. Imagine that i have from a1:a1000 cells with email adress. I want get all domains in that cells. supose in A1: teste@tes.com A2: teste@pl.com A3: dfdsf@kon.org I want one formula that could me give all information after the "@". lik tes.com, pl.com and kon.org Because i want manager the domains in my antispam and need to know that kind of information. its possible ? =RIGHT(A1, LEN(A1)-FIND("@",A1,1)) if A1 contains your email address. Priya "Fire" <Fire@discussions.microsoft.com> wrote in message news:FA3EDCF0-CAF4-4A91-9D12-F2D55408C5DD@micr...

Pen created using ExtCreatePen fails to get selected into a metafi
Hello, In the OnDraw() function of my ActiveX control(just drawing , I select a Pen as shown below. The constructor of CPen passes the parameters to create EMR_EXTCREATEPEN structure. ( ExtCreatePen also creates a similar structure) LOGBRUSH tmpLB; tmpLB.lbColor = COLORREF(RGB(0,0,0)); tmpLB.lbStyle = BS_SOLID; CPen tempPen(PS_GEOMETRIC|PS_DOT, 5, &tmpLB,0,NULL); pOldPen = (CPen*)pdc->SelectObject(&tempPen); VERIFY(pOldPen); pdc->Ellipse(rc); On Screen, the ellipse draws correctly with dots of width 5. But, On printing or drawing to a metafile DC ...

m_hWnd getting lost its value in OnKillActive() function of Property Page.
Hi, I am doing an application on property sheet.I have added an 4 property pages containing some controls on each page, to the property sheet and the application is running fine. I have used an API ---> OnKillActive() to get the control values and put into the structure( which i have defined). But the problem is, the m_hWnd is getting zero within the OnKillActive() function. This is happening only propertypage3 -OnKillActive() not in others.Even i haven't found any compile time or run time errors in the code. Can you just tell me what might be the reason for m_hWnd to become zero...

When I filter data, sometimes I do not get row count retrived
When you are using auto filter on a worksheet and then you pick a valu to filter, you get the list of rows that satisfy that criteria. Also in the status bar, you get the number of rows retrived as a result o this filter. Example you will get "3 of 25 rows found" or somethin like that. Sometimes I do not get that message, I just get a messag called filter mode. Why does this happen and how can I correct this? Thank you -- Message posted from http://www.ExcelForum.com If your worksheet has any formulae on it they automatically calculat after filtering thereby suppressing the messa...

Pivot Tables: Can I get cells to duplicate downward automatically?
I am currently using PivotTables and am wondering if there is a way for the table to automatically appear with all of the cells 'filled-in' down a column without having to do it manually after it appears? Do you mean when the source data has been changed? If so you can use a dynamic table and refer to it then just refresh the pivot table there are instructions for here http://www.contextures.com/xlPivot01.html Regards, Peo Sjoblom "StrawberryGirl" wrote: > I am currently using PivotTables and am wondering if there is a way for the > table to automatically ...

Strange change in email from workflow when applying service pack 2
Hello, We have a workflow running to inform our relations of the state a incident is in. Before service pack 2 the email always wend to the first contact of an account.... After appying sp2 it is send to the email of the account! With other words, our workflow does not work correctly anymore, it is missing contact data. Can someone tell me why this change is applied in sp2? by design? error? Hope someone can enlighten me, thanks in advance... Eric van der Niet ------=_NextPart_0001_1633E9C9 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Eric, Thank you for the post and ...

Changing Numebers on Y Axis, without affecting Graph
I have a simple bar I created showing sites that have construction done. Is there anyway to have excel convert my data into a % (out of 100%) and graph my items? I do not want to do the math for each individual peice of data. Example: I have on peice of data that basically says 4 out of 5 were completed. How can I make excell chart that as 90%? Well, first of all, 4 out of 5 is 80%. Second, charts plot whatever is input. If you want to plot percentages, you have to calculate them, and plot the calculations. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.c...

supplier info gets deleted
I have run a report for items in a category in order to organize them into the proper categories & departments I double click the item look up - properties opens - I change the deparment &/or category - OK When I look at these items again - the supplier information is gone - I have to reopen each one and re-enter the supplier! Can anyone tell me why & how to avoid this happening? ALSO in my category report I am finding some suppliers blank - but when I open the properties the supplier is there - I have to delete the supplier & re-add the supplier. Anyone know why &am...

Changing Project Calendar Mid Project Schedule
We have a schedule that was started using an 8 hour calendar and we are midway through the schedule, due to a change in business we want to change the project calendar to a 6 hour day, and change the resource Max Units to 75% in ERP. Is this possible? And, if it is, what are the pitfalls, e.g., started tasks? breynold55 -- By now you have probably noticed that your question is not getting any replies. This is because changing the Project Calendar to a 6-hour day in mid-stream on a project is simply NOT a good idea, and will open you to a world of pain. A much BETTER approach ...

How do I get rid of #agg fields when Importing an XML file into E.
I'm importing an XML file that has int and double information within it. When the data is imported into Excel I get an extra column tagged with #agg For example: - <NewDataSet> - <Results> <FOO>41.01</FOO> </Results> This creates two columns when importing to Excel /Results/FOO /Results/FOO/#agg 41.01 41.01 ...

Max number of items in RMS SO database?
Is there a limit to the number of items in the RMS SO (1.2/1.3) database? I've gotten a call from a store manager and he tells me that when they try to add an item they get a "run time error 6 - overflow". His solution is to delete an "old" item and then a new item can be added. I find it hard to beleive there is a limit, since this store has been open for many years and the database has never been shrunk, meaning that every transaction from day one is stored... Last time I looked it was like 9GB (yeah, I know, we need to fix this). So why the trouble with items...

problem with mail database
Thank you all who have assisted with my troubles. I have now rescued all but a couple of mail boxes - those with odd names - more exeriments necessary I guess. Neil ...

Get names of closed workbooks?
I'm a relative newbie at VB but I've managed to cobble together bits of code gleaned from this NG which enables me to extract selected data from a group of closed workbooks located in a particular folder ( located at "C:\Temp\Survey) and then save it to another workbook called Update.xls, in worksheet called Get Data. This is based on two macros - the most significant of which is based on a development originally done by Nikos Yannacopoulos. I want to write an extra couple of lines that will get the name of each closed workbook in the folder. Here is the code for both macros. the...

When I try to insert a page number I can't. Get error message???
When I try to insert a page number in Word all I get is a "save selection to page number gallery." But you can't click on it. Then an error message will come up that says something about not being able to do this. This has just started happening. this is the first time I couldn't add one. I am working on a graduate paper and have to have page numbers, help!! Exit Word and then delete the Building Blocks.dotx file in your user profile. When you restart Word, a new copy of the file with factory default settings will be recreated. Note that you'll fin...

Database Design 01-05-10
I need help designing a database. I work for a non-profit company that offers a myriad of services to the public. Each customer that works with us is assigned a customer ID #. I called the first table of the database "General Information" which includes name, address, contact info, etc. In that table i used the customer ID # as the primary key. So far this is simple. However each customer does not use every service that we offer and each of these services do not have a specific ID #. Up to this point i have created tables for each of the services we offer. Each o...

Y axis change form numbers to text
I am trying to plot my students performance in each of their assignments by producing a line graph to show whether they have moved up grades but cannot chnage the numbers on the y axis to grade letters is this possible? The y-values must be numbers - that it how Excel knows how high to place them You could use a conversion table (90 and above = A, 80 and above = B, etc Then use one of the chart labeler addins (I recommend Bovey's) Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Tushar's Hover Chart Label utility:...

How do I change the y-axis on an Excel chart from numbers to words
I am plotting numbers which represent an activity for a machine. I would like to substitute a description of the activity for the number on the chart. Does anyone know how to do this? I hope this answers your question: 1.Right click on the chart 2.Select 'Source Data' 3.Select the 'Series' tab 4.In 'Values', clear the box and select the list of words you want to appear on the Y axis (have this list written somewhere on a worksheet) 5.Click ok Was this what you were after? "WDGILBERT" wrote: > I am plotting numbers which represent an activity for a ...