Incomplete installation of AutoAudit

Jul 11, 2014 at 5:53 AM
Hi,

First of all, does AutoAudit work with SQL Server 2014 Express? I realize it may not be tested/supported, but is it specifically known not to work properly with SS2K14E?

I'm trying to install AutoAudit on a new database. What I did:
  • Create the database
  • Created ~ 10 empty tables via scripts. No data loaded yet.
  • Ran the AutoAudit script
Various tables and views were created in the Audit schema. But, no new or triggers columns in my base tables, and no views for _RowHistory and _Deleted.

Am I mis-reading the documentation?

Here is an excerpt from my configuration:
--*******************************************************
--          VARIABLE INITIALIZATION
--*******************************************************
Set @AuditSchema                    = 'Audit'           --This is the schema to use for the AutoAudit objects. Edit for your database

Set @ViewSchema                     = '<TableSchema>'   --This is the schema to use for the AutoAudit base table views. Edit for your database.
                                                        --<TableSchema> = the _RowHistory and _Deleted views have the same schema as the base table.

Set @Version                        = '3.30a'           --leave this unless you are making changes to this script

Set @OptimizeForAudit               = 0                 --@OptimizeForAudit = 0 creates an index to speed up views
                                                        --(and slows down AutoAudit), @OptimizeForAudit = 1 keeps AutoAudit 10% faster 
                                                        --but the reporting views are slower.

Set @RebuildTriggersAfterInstall    = 1                 --@RebuildTriggersAfterInstall = 1 launches pAutoAuditRebuildAll after
                                                        --this script has completed and AutoAudit has been updated
                                                        --@RebuildTriggersAfterInstall = 0 runs this script to update AutoAudit but does NOT 
                                                        --execute pAutoAuditRebuildAll. This may cause problems with your existing AutoAudit
                                                        --triggers views and UDFs.

Set @DetailedMigrationCheck         = 0                 --This is only applicable if you are upgrading your AutoAudit environment
                                                        --from version 2.00h to 3.20. The detailed verification could take several minutes to
                                                        --complete.
                                                        --0 = quick check with rowcount only, 1 = detailed record verification

Set @WithLogFlag                    = 0                 --This flag determines if the "With Log" function is included in the raiseerror 
                                                        --statements or not
                                                        --this is added because some DBA's may not have rights to write to the Windows log
                                                        --0 = exclude "with log", 1 = include "with log"

--set DDL column names
--*** make sure these DO NOT require quotename() (no spaces, special characters etc.)
--*** IF YOU ARE UPGRADING FROM AUTOAUDIT 2.X set the column names to Created, CreatedBy, Modified, ModifiedBy and RowVersion 
--    otherwise the upgrade process will add new columns. 
--    YOU CAN CHANGE THE NAMES LATER IN THE BASE TABLES AND THE AUTOAUDITSETTINGS TABLE.
Set @CreatedColumnName              = 'AutoAudit_CreatedDate'
Set @CreatedByColumnName            = 'AutoAudit_CreatedBy'
Set @ModifiedColumnName             = 'AutoAudit_ModifiedDate'
Set @ModifiedByColumnName           = 'AutoAudit_ModifiedBy'
Set @RowVersionColumnName           = 'AutoAudit_RowVersion'

Set @DateStyle                      = '121'             --this variable identifies the date style you wish to use when inserting data into
                                                        --the AuditHeader table. It is recommended you only use a style that provides full
                                                        --datetime precision with century. These are the tested and allowed choices.
                                                        -- 113 : 26 Nov 2013 13:20:54:553
                                                        -- 121 : 2013-11-26 13:22:55.170
                                    
--Set object prefixes and suffixes
Set @ViewPrefix                     = 'vw'              --User configurable - sets the PREFIX for _RowHistory, _Deleted views
Set @UDFPrefix                      = 'fn'              --User configurable - sets the PREFIX for _RowHistory, _TableRecovery functions
Set @RowHistoryViewSuffix           = '_RowHistory'     --User configurable - sets the suffix for "_RowHistory" views
Set @DeletedViewSuffix              = '_Deleted'        --User configurable - sets the suffix for "_Deleted" views
Set @RowHistoryFunctionSuffix       = '_RowHistory'     --User configurable - sets the suffix for "_RowHistory" functions
Set @TableRecoveryFunctionSuffix    = '_TableRecovery'  --User configurable - sets the suffix for "_TableRecovery" functions
Coordinator
Jul 11, 2014 at 10:00 AM
Hi,

Did you execute the Audit.pAutoAudit stored procedure to add the AutoAudit triggers/views etc to your tables?
Here are the parameters with defaults...

exec Audit.pAutoAudit
@SchemaName = 'dbo',        --this is the default schema name for the tables getting AutoAudit added
@TableName ,                    --enter the name of the table to add AutoAudit to.
@ColumnNames= '<All>',      --columns to include when logging details (@Log...=2). Default = '<All>'. Format: '[Col1],[Col2],...'
@StrictUserContext   = 1,       -- if 0 then permits DML setting of Created, CreatedBy, Modified, ModifiedBy
@LogSQL  = 0,               -- 0 = Don't log SQL statement in AuditHeader, 1 = log the SQL statement
@BaseTableDDL = 0,          -- 0 = don't add audit columns to base table, 1 = add audit columns to base table
@LogInsert = 2,             -- 0 = nothing, 1 = header only, 2 = header and detail
@LogUpdate   = 2,               -- 0 = nothing, 1 = header only, 2 = header and detail
@LogDelete = 2              -- 0 = nothing, 1 = header only, 2 = header and detail

You can also use exec Audit.pAutoAuditAll to add the triggers to all tables in your database except the ones listed in the AuditAllExclusions tabe.
Jul 14, 2014 at 2:10 AM
Hi,

I replied via my email client but it didn't show up. Apologies if this displays twice.

I saw this in the script:
Set @RebuildTriggersAfterInstall    = 1  --@RebuildTriggersAfterInstall = 1 launches pAutoAuditRebuildAll after
                                         --this script has completed and AutoAudit has been updated
                                         --@RebuildTriggersAfterInstall = 0 runs this script to update AutoAudit but does NOT
                                         --execute pAutoAuditRebuildAll. This may cause problems with your existing AutoAudit
                                         --triggers views and UDFs.
and thought it would take care of that. In particular, that I would see triggers on the source tables.

However, I may have misunderstood these instructions:
Set @AuditSchema                    = 'Audit' --This is the schema to use for the AutoAudit objects. Edit for your database
Is this meant to be set to the schema for the source objects, or the schema for the target objects created by AutoAudit? For example, my source tables are [dbo].[whatever], I set @AuditSchema to 'Audit' i.e. the schema for the AutoAudit target objects, and after running the script, I had tables [Audit].[AuditAllExclusions], etc.

I ran:
exec Audit.pAutoAudit

@SchemaName = 'dbo',        --this is the default schema name for the tables getting AutoAudit added
@TableName = dim_Users,     --enter the name of the table to add AutoAudit to.
@ColumnNames= '<All>',      --columns to include when logging details (@Log...=2). Default = '<All>'. Format: '[Col1],[Col2],...'
@StrictUserContext   = 1,   -- if 0 then permits DML setting of Created, CreatedBy, Modified, ModifiedBy
@LogSQL  = 0,               -- 0 = Don't log SQL statement in AuditHeader, 1 = log the SQL statement
@BaseTableDDL = 0,          -- 0 = don't add audit columns to base table, 1 = add audit columns to base table
@LogInsert = 2,             -- 0 = nothing, 1 = header only, 2 = header and detail
@LogUpdate   = 2,           -- 0 = nothing, 1 = header only, 2 = header and detail
@LogDelete = 2              -- 0 = nothing, 1 = header only, 2 = header and detail
But got these errors (but they may be related to the above):
Creating AutoAudit for table: [dbo].[dim_Users]
  Options:  @StrictUserContext=1, @LogSQL=0, @BaseTableDDL=0, @LogInsert=2, @LogUpdate=2, @LogDelete=2, @AuditSchema='Audit', @ColumnNames='<All>'
  Dropping existing AutoAudit components
  Creating Insert trigger
  Creating Update trigger
  Creating Delete trigger
  Creating _Deleted view
Msg 102, Level 15, State 1, Procedure vwdim_Users_Deleted, Line 19
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Procedure vwdim_Users_Deleted, Line 43
Incorrect syntax near the keyword 'AS'.
  Creating _RowHistory view
  Creating _RowHistory UDF
  Creating _TableRecovery UDF
Kind Regards,
Scott
Jul 14, 2014 at 3:33 AM
After further testing:

1) I now understand that @AuditSchema should be the schema for the target objects created by AutoAudit.

2) These are my current settings (I've deleted the comments for better formatting):
--*******************************************************
--          VARIABLE INITIALIZATION
--*******************************************************
Set @AuditSchema                    = 'Audit'           
Set @ViewSchema                     = 'Audit'           
Set @Version                        = '3.30a'           
Set @OptimizeForAudit               = 0                 
Set @RebuildTriggersAfterInstall    = 0                 
Set @DetailedMigrationCheck         = 0                 
Set @WithLogFlag                    = 0                 

--set DDL column names
--*** make sure these DO NOT require quotename() (no spaces, special characters etc.)
--*** IF YOU ARE UPGRADING FROM AUTOAUDIT 2.X set the column names to Created, CreatedBy, Modified, ModifiedBy and RowVersion 
--    otherwise the upgrade process will add new columns. 
--    YOU CAN CHANGE THE NAMES LATER IN THE BASE TABLES AND THE AUTOAUDITSETTINGS TABLE.
Set @CreatedColumnName              = 'CreatedDate'
Set @CreatedByColumnName            = 'CreatedBy'
Set @ModifiedColumnName             = 'ModifiedDate'
Set @ModifiedByColumnName           = 'ModifiedBy'
Set @RowVersionColumnName           = 'RowVersion'

Set @DateStyle                      = '121'             
                                                        
--Set object prefixes and suffixes
Set @ViewPrefix                     = ''                
Set @UDFPrefix                      = 'f'               
Set @RowHistoryViewSuffix           = '_RowHistory'     
Set @DeletedViewSuffix              = '_Deleted'        
Set @RowHistoryFunctionSuffix       = '_RowHistory'     
Set @TableRecoveryFunctionSuffix    = '_TableRecovery'  
3) pAutoAuditDropAll has an error if both @ViewPrefix and @UDFPrefix is blank.

4) The problem raised in my original post is due to the default for @BaseTableDDL=0. When I changed it to 1, I got improved behavior (but with errors, see below)

5) So, what I've done now is:
a) Set @RebuildTriggersAfterInstall = 0
b) Run AutoAudit
c) I then run this code after b):

EXECUTE @RC = [Audit].[pAutoAuditAll]
@StrictUserContext=1
,@LogSQL=0
,@BaseTableDDL=1
,@LogInsert=2
,@LogUpdate =2
,@LogDelete=2
GO

EXECUTE @RC = [Audit].[pAutoAuditRebuildAll]
GO

I assume this is the best and/or acceptable approach?

I could have changed the default for pAutoAuditAll @BaseTableDDL to 1, but I want to keep the main script as pristine as possible.

6) I believe these are minor doc bugs in the notes:
--------------------------------------------------------------------
--------------------------------------------------------------------
* <AuditSchema>.pAutoAudit Procedure

applies AutoAudit to a single table

parameters: 
  @SchemaName sysname - the schema of the table (default = 'dbo') 
  @TableName sysname - the name of the table (required)
     (sysname is NVARCHAR(128))
  
  @StrictUserContext BIT (default = 1)
  @LogSQL BIT (Default = 0)
  @BaseTableDDL BIT (Default = 0)
  @LogInsert TINYINT (Default = 2)    
  @LogUpdate TINYINT (Default = 2)    
  @LogDelete TINYINT (Default = 2)    

... then later ...

--- 
@BaseTableDDL determines if the Created, CreatedBy, Modified, ModifiedBy 
and RowVersion columns are added to the base tables    
    0 = make no changes to the base tables
    1 = (default) add the Created, CreatedBy, Modified, ModifiedBy, and 
        RowVersion columns to the base tables
Not a biggie just wanted to point it out.

7) However, I'm getting errors when I run AutoAudit per #5 above. Here is an excerpt from the output:
Jul 14 2014 12:33PM
Installing AutoAudit in database: Midas
AutoAudit schema has been set to: [Audit]
Creating AutoAudit tables
Creating AutoAudit table indexes
   Creating indexes on AuditHeader.PrimaryKey
   Creating indexes on AuditDetail.AuditHeaderID
   Creating indexes on AuditBaseTables
Creating View - vAudit (view for legacy Audit table)
 
Searching for existing AutoAudit components
Dropping previous revision of AutoAudit components
Creating DDL Trigger
Creating View - vAuditArchive (view for Archive Audit table)
Creating View - vAuditAll
Creating View - vAuditHeaderAll
Creating View - vAuditDetailAll
Creating Stored Procedure - pAutoAuditArchive
Creating Stored Procedure - pAutoAudit
Creating Stored Procedure - pAutoAuditRebuild
Creating Stored Procedure - pAutoAuditRebuildAll
Creating Stored Procedure - pAutoAuditDrop
Creating Stored Procedure - pAutoAuditAll
Creating Stored Procedure - pAutoAuditSetTriggerState
Creating Stored Procedure - pAutoAuditSetTriggerStateAll
Creating Stored Procedure - pAutoAuditDropAll
 
 
*** Execute pAutoAuditRebuild or pAutoAuditRebuildAll to upgrade AutoAudit triggers and views. ***
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Deleted, Line 19
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Deleted, Line 52
Incorrect syntax near the keyword 'AS'.
Creating AutoAudit for table: [dbo].[dim_Addresses]
  Options:  @StrictUserContext=1, @LogSQL=0, @BaseTableDDL=1, @LogInsert=2, @LogUpdate=2, @LogDelete=2, @AuditSchema='Audit', @ColumnNames='<All>'
  Dropping existing AutoAudit components
  Adding Base Table DDL
  Creating Insert trigger
  Creating Update trigger
  Creating Delete trigger
  Creating _Deleted view
  Creating _RowHistory view
  Creating _RowHistory UDF
  Creating _TableRecovery UDF
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Contacts_Deleted, Line 19
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Server XXXXXXXX, Procedure dim_Contacts_Deleted, Line 50
Incorrect syntax near the keyword 'AS'.
Creating AutoAudit for table: [dbo].[dim_Contacts]
  Options:  @StrictUserContext=1, @LogSQL=0, @BaseTableDDL=1, @LogInsert=2, @LogUpdate=2, @LogDelete=2, @AuditSchema='Audit', @ColumnNames='<All>'
  Dropping existing AutoAudit components
  Adding Base Table DDL
  Creating Insert trigger
  Creating Update trigger
  Creating Delete trigger
  Creating _Deleted view
  Creating _RowHistory view
  Creating _RowHistory UDF
  Creating _TableRecovery UDF
etc for all my tables.

8) Furthermore, I also need a timestamp column in my tables. When I add that column to my tables, I get different errors. Excerpt:
*** Execute pAutoAuditRebuild or pAutoAuditRebuildAll to upgrade AutoAudit triggers and views. ***
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Audit_Insert, Line 112
Incorrect syntax near ','.
Creating AutoAudit for table: [dbo].[dim_Addresses]
  Options:  @StrictUserContext=1, @LogSQL=0, @BaseTableDDL=1, @LogInsert=2, @LogUpdate=2, @LogDelete=2, @AuditSchema='Audit', @ColumnNames='<All>'
  Dropping existing AutoAudit components
  Adding Base Table DDL
  Creating Insert trigger
Msg 15165, Level 16, State 1, Server XXXXXXXX, Procedure sp_settriggerorder, Line 142
Could not find object '[dbo].[dim_Addresses_Audit_Insert]' or you do not have permission.
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Audit_Update, Line 104
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Audit_Update, Line 221
Incorrect syntax near ','.
  Creating Update trigger
Msg 15165, Level 16, State 1, Server XXXXXXXX, Procedure sp_settriggerorder, Line 142
Could not find object '[dbo].[dim_Addresses_Audit_Update]' or you do not have permission.
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Audit_Delete, Line 107
Incorrect syntax near ','.
  Creating Delete trigger
Msg 15165, Level 16, State 1, Server XXXXXXXX, Procedure sp_settriggerorder, Line 142
Could not find object '[dbo].[dim_Addresses_Audit_Delete]' or you do not have permission.
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Deleted, Line 19
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_Deleted, Line 52
Incorrect syntax near the keyword 'AS'.
  Creating _Deleted view
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure dim_Addresses_RowHistory, Line 52
Incorrect syntax near ','.
  Creating _RowHistory view
Msg 102, Level 15, State 1, Server XXXXXXXX, Procedure fdim_Addresses_TableRecovery, Line 163
Incorrect syntax near ','.
  Creating _RowHistory UDF
  Creating _TableRecovery UDF
I named my timestamp column ts, ts, and dummy, always with the same errors as above.

9) It would be cool if pAutoAudit accepted a @ColumnNames setting like "All except ...". For example, @ColumnNames = '<ALL>, -TS, -DUMMY1, -DUMMY2', etc. I won't need to track changes to the TimeStamp column, so would like to exclude this from the <ALL> list. However, I understand this would be a lot of string parsing for an easy workaround (seed AuditBaseTables). So, I'll need to seed AuditBaseTables with a "manual" list of columns and re-run pAutoAuditRebuildAll (I assume that's the correct approach???)

10) Question: If I manually create the DDL column names in my table creation code, then set @BaseTableDDL=0, will AutoAudit use those columns in all the triggers, etc, as though I'd used @BaseTableDDL=1?

Thanks, and apologies for the length.

Kind Regards,
Scott