Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Imaginative Auditing with Rollback (Undo) and RollForward (Redo) Part II Expand / Collapse
Author
Message
Posted Thursday, August 6, 2009 4:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 11,192, Visits: 11,089
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
Posted Thursday, August 6, 2009 5:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 645, Visits: 1,842
Thanks, Paul - High praise indeed! When used appropriately XML certainly has it's place in the SQL world. I'm sure you'll agree
Post #766107
Posted Thursday, August 6, 2009 7:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:12 AM
Points: 1,207, Visits: 1,259
//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
Posted Thursday, August 6, 2009 11:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:12 AM
Points: 1,207, Visits: 1,259
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
Posted Friday, August 7, 2009 12:31 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 645, Visits: 1,842
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 »

Add to briefcase ««12

Permissions Expand / Collapse