Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II

Question

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II

David McKinney

SSChampion

Points: 10472
More actions
April 17, 2009 at 9:24 pm

#384389

Comments posted to this topic are about the item Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

nancy.lytle Default port Points: 1430 More actions · Answer 1

I am having trouble getting the SSIS to work. I should mention I am using SS 2005 and VS 2005 and that I am a novice as SSIS and XML.

I created the Control FLow objects, Get XML from Database and the Transform XML with XSL into HTTP. What I am having problems with is the Transform object, First issue is what XML files to place where in the Transform task. I have set the input as the transform.xsl and the output as the files where I want the info to go and the Second Operand as the saved result (saved as DBTables.xml) from the SQL script which created the XML for the tables in the database. However when I try and run the package I get the following error:

[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

So I don't know if I have the files in the wrong places or what. Plus when I tried to validate the XML file generated from the script it has the error:

Missing document type Declaration [DTD]

Any assistance would be greatly appreciated.

Nancy

David McKinney SSChampion Points: 10472 More actions · Answer 2

Hi Nancy,

I'll try to check this out this evening and send you some feedback. (It's afternoon here.)

Regards,

David.

(Alternatively checkout MSXSL from the Microsoft downloads pages.)

David McKinney SSChampion Points: 10472 More actions · Answer 3

Nancy,

It looks to me that you've not set the OperationType correctly in the XML task. (Your doing XSD validation instead of XSLT.)

I've attached a screenshot of how the xml task should look.

I created an SSIS package with just one task, and configured it as per the screenshot.

You should save the xml (from the query) and the XSL from the article, and then point to them in the task. (You'll have to create new text file connections.)

Make sure the SaveOperationResult is set to true and correctly configured to.

(Just follow the attached screenshot.)

Let me know how it goes,

David.

nancy.lytle Default port Points: 1430 More actions · Answer 4

Looks like I got it to work and oh goodness, it looks like it is going to be fun to play with!

Thanks for everything,

Nancy

David McKinney SSChampion Points: 10472 More actions · Answer 5

nancy.lytle (5/11/2009)
Looks like I got it to work and oh goodness, it looks like it is going to be fun to play with!
Thanks for everything,
Nancy

excellent! enjoy!

Mik-BI SSC Journeyman Points: 83 More actions · Answer 6

A great concept, excellently packed and ready for use out-of-the-box.

Reading the article sparked off an idea but, as I'm relatively inexperienced in working with SQL server & XML, I'm not sure if this would be possible. Anyway here is the idea.

In applications that I have worked on there has been a need, especially in the area of master data (codes, descriptions, customers account information etc.), to record who changed what data and when. Something that can take as much effort to do as to maintain the information itself.

Extending the data collected to include the who & the when will allow the rollback, rollforward of data on a user basis and solve the business auditing need in one and the same solution.

I'm assuming that the system tim & the logged on user (not necessarily the db user) are available as either variables or function calls.

To me, this almost seems too good to be true. There must be a catch somewhere or what?

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 7

Steve Jones - SSC Editor

SSC Guru

Points: 736172

May 18, 2009 at 7:34 am

#996314

Good job on the series, David!

David McKinney SSChampion Points: 10472 More actions · Answer 8

mcl (5/18/2009)
A great concept, excellently packed and ready for use out-of-the-box.
Reading the article sparked off an idea but, as I'm relatively inexperienced in working with SQL server & XML, I'm not sure if this would be possible. Anyway here is the idea.
In applications that I have worked on there has been a need, especially in the area of master data (codes, descriptions, customers account information etc.), to record who changed what data and when. Something that can take as much effort to do as to maintain the information itself.
Extending the data collected to include the who & the when will allow the rollback, rollforward of data on a user basis and solve the business auditing need in one and the same solution.
I'm assuming that the system tim & the logged on user (not necessarily the db user) are available as either variables or function calls.
To me, this almost seems too good to be true. There must be a catch somewhere or what?

To include the Who and the When is trivial - you can just add columns with default values to the audit table. (e.g. getdate() and suser_sname).

What I provided is the core of the solution - and while it does quite a bit 'out of the box' you'll still have to adapt / interpret and apply in order to get it working for your situation.

Is there a catch? Yes, probably - although in certain circumstances it might be just what you're looking for. The basic 'issues' are principally (1) that the trigger is quite chunky, and so may take as much time / resources as your operation (2) Everything here is auditing to the same table so there may be competition to insert rows.

Essentially you've got to consider the possibility that the auditing may bring your database to a halt, if you're not careful. But either for specific tables or for a database with few updates, there may be no problem at all.

Regards,

David.

Mik-BI SSC Journeyman Points: 83 More actions · Answer 9

David McKinney (5/18/2009)
To include the Who and the When is trivial - you can just add columns with default values to the audit table. (e.g. getdate() and suser_sname).
What I provided is the core of the solution - and while it does quite a bit 'out of the box' you'll still have to adapt / interpret and apply in order to get it working for your situation.
Is there a catch? Yes, probably - although in certain circumstances it might be just what you're looking for. The basic 'issues' are principally (1) that the trigger is quite chunky, and so may take as much time / resources as your operation (2) Everything here is auditing to the same table so there may be competition to insert rows.
Essentially you've got to consider the possibility that the auditing may bring your database to a halt, if you're not careful. But either for specific tables or for a database with few updates, there may be no problem at all.
Regards,
David.

I've just noted that I didn't sign my comment, please accept my apologies.

As with the catches, I'd already got my solution orientated blinkers on. You're absolutely right it would potentially require lots of hardware to fully audit an OLTP system database, I was looking at the solution for low change Master Data tables, which should keep database resource use and contention within the bounds of acceptability.

Best regards

Mik

Paul White SSC Guru Points: 150467 More actions · Answer 10

Very cool! Rated five stars - I particularly like the effort made with the article 's presentation, the structure of the SQL (CTEs and nested FOR XML are a happy combination), and the XML Spy diagram. Excellent stuff!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

David McKinney SSChampion Points: 10472 More actions · Answer 11

Thanks, Paul - High praise indeed! When used appropriately XML certainly has it's place in the SQL world. I'm sure you'll agree 😉

matt6288 SSCertifiable Points: 5058 More actions · Answer 12

//EDIT -- Nevermind, apparently if you actually pay attention to the steps everything works just find. Sorry about that.

I can't figure out what I have done wrong with the SSIS package. I am getting this error on the execute XML task:

[XML Task] Error: An error occurred with the following error message: "XSLT compile error.".

Here are my values:

OperationType = XSLT

SourceType = File Connection

Source = transform.xsl

SaveOperationResult = TRUE

DestinationType = Fil Connection

OverwriteDestination = TRUE

SecondOperandType = Variable

SecondOperand = User::XmlAsString

Any clue on what I need to change?

Thanks

matt6288 SSCertifiable Points: 5058 More actions · Answer 13

I did run into one problem with the trigger scripts. Since the quotename function is used the argument is of type sysname which is nvarchar(128). If you have a field longer than this it nulls out the entire field and since the bigAudit table does not allow any null values in any column the trigger fails. I have changed the 1 trigger that I ran into by just including the quotes myself. So for any column that might have data longer than 128 I changed the insert/delete/update sections of the trigger from:

QUOTENAME(i.[Column Name],'''')

to

(''''+i.[Column Name]+'''')

Before making this change I would get this error:

Msg 515, Level 16, State 2, Procedure trgAuditscripts, Line 35

Cannot insert the value NULL into column 'RollForwardSQL', table 'Audit.dbo.BigAudit'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Just wanted to post this in case anyone else was having problems.

David McKinney SSChampion Points: 10472 More actions · Answer 14

Good find, Matt!

I completely forgot / overlooked that quotename takes a sysname parameter.

I used it principally to take care of strings which already have a quote in them. Quotename will double them up, if you know what I mean. o'leary will become 'o''leary'. I guess I can just use the replace function, or write a custom quotename function which is less restrictive than the original!

Thanks a lot for sharing your find with us. I hope you have fun with your auditing!

Regards,

David.