Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11322 Visits: 11354
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
SQLblog.com
@SQL_Kiwi
David McKinney
David McKinney
SSC Eights!
SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)

Group: General Forum Members
Points: 893 Visits: 2090
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
matt6288
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1238 Visits: 1351
//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
matt6288
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1238 Visits: 1351
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
David McKinney
SSC Eights!
SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)SSC Eights! (893 reputation)

Group: General Forum Members
Points: 893 Visits: 2090
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search