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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David McKinney » Imaginative Auditing with Rollback (Undo) and...

15 posts, Page 2 of 2

««1 2

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

Rate Topic

Display Mode

Topic Options

Author

Message

Paul White

Posted Thursday, August 06, 2009 4:47 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:43 PM
Points: 10,990, Visits: 10,576

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #766073

David McKinney

Posted Thursday, August 06, 2009 5:53 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:10 AM
Points: 583, Visits: 1,611

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

Post #766107

matt6288

Posted Thursday, August 06, 2009 7:13 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:11 PM
Points: 1,132, Visits: 1,182

//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

Post #766167

matt6288

Posted Thursday, August 06, 2009 11:20 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:11 PM
Points: 1,132, Visits: 1,182

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.

Post #766402

David McKinney

Posted Friday, August 07, 2009 12:31 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 1:10 AM
Points: 583, Visits: 1,611

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.

Post #766712

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions