Error publishing SSDT project with AutoAudit 3.30a to the same database that the SSDT was modeled against

Jul 28, 2014 at 7:37 PM
Good afternoon,

I have a SQL2008 database on which I have installed AutoAudit 3.30a. In order to properly control changes to this database, I have created an SSDT project for that database. However, when I try to publish the SSDT, it fails saying "Errors occurred while modeling the target database. Deployment can not continue." If I run the pAutoAuditDropAll stored proc telling it to drop the base table objects (DDL Columns, Triggers, and Views) only, I can successfully publish the SSDT (which will add all that stuff back because it's in the project). This, of course, is not ideal because I lose all of the information from the DDL columns.

How can I successfully deploy the SSDT without having to drop the base table objects?
Jul 28, 2014 at 8:02 PM

I think what's hapenning is that your database model within SSDT does not include the AutoAudit Base Table DDL columns. When you publish, SSDT wants to remove those columns from your tables.
Have you tried adding the 4 AutoAudit columns to your model and then publishing?
Another option could be to not add the DDL columns tou your tables. If AutoAudit is added to a table with @StrictUserCentext = 1 you could set @BaseTableDDL = 0. With these options, the 4 AutoAudit columns don't get added to the table.
Jul 28, 2014 at 8:26 PM
The SSDT does, in fact, contain the DDL columns (Created, CreatedBy, Modified, ModifiedBy, and RowVersion). I was able to narrow it down to the triggers - if I remove the base table triggers (using the pAutoAuditDropAll stored proc with just DropBaseTableTriggers = True (the rest set to False), I am able to publish the SSDT successfully.
Jul 29, 2014 at 7:26 PM
More Details.....

The database holds our test automation data. We originally applied AutoAudit 2.0 to it. We then added tables, changed a few of the table names, added/updated/removed columns, etc but we never reran the AutoAudit 2.0 SPs after these changes were made. When we ran the AutoAudit 3.30a upgrade, we first had to clean up some of the old AutoAudit 2.0 objects that had become very out of sync with the tables they were watching. Once we had a successful AutoAudit 3.30a upgrade, I created the SSDT project against the upgraded database. I then took a backup of the upgraded database, restored in on my local machine, and tried to publish the SSDT to my local database copy where I got the error stated in the OP.

One of the things that had to be cleaned up in order for the AutoAudit 3.30 upgrade to work was the removal of the [TableName]_Created_df, [TableName]_CreatedBy_df, [TableName]_Modified_df, [TableName]_ModifiedBy_df, and [TableName]_RowVersion_df constraints on the tables that had been renamed. The constraints are still in the rest of the tables, but the AutoAudit 3.30 script and SPs didn't recreate them on the tables where they were manually removed. Are those constraints necessary for AutoAudit 3.30 to work it's magic?

Additionally, here are the steps I did in order to get the SSDT (which has all of the audit tables, triggers, and views as well as the base table DDL columns, triggers, and views) to publish successfully.
1) Restore a fresh backup of the database to my local machine (for testing)
2) Manually remove the constraints (in the local copy of the database) that prevent the pAutoAuditDropAll SP from working properly
3) Run pAutoAuditDropAll with the following values (in the local copy of the database)
- DropAuditTables = False
- DropAuditViews = False
- DropAuditSPs = False
- DropAuditDDLTriggers = False
- DropBaseTableDDLColumns = False
- DropBaseTableTriggers = __True__
- DropBaseTableViews = False
- ConfirmDropAll = Yes
4) Run pAutoAuditAll with the following values (in the local copy of the database)
- StrictUserContext = 0
- LogSQL = 1
- BaseTableDDL = 1
- LogInsert = 2
- LogUpdate = 2
- LogDelete = 2
5) Publish the SSDT to the local copy of the database (successfully)
6) Publish the SSDT to the local copy of the database (successfully) again to make sure that it wasn't something funky in the SSDT that was causing the problem in the first place.

Any idea what might have caused the problem in the first place? (I know it's a tough question without seeing the database, but I thought I'd ask.)