Bug if PrimaryKey is not an Int

Mar 12, 2013 at 4:51 PM
First, I'm very impressed with the scripting and how much work it saves me.
Secondly, I wanted to share a bug I found in 2.0.0h. The scenario is if the primaryKey is NOT an int, then the _RowHistory Table-Valued UDF is generated with an assumption that the PK passed in would be an int.

Orig code:
SET @SQL = 'CREATE FUNCTION ' + @SchemaName + '.' + @TableName + '_RowHistory (@PK __INT__)' + Char(13) + Char(10)
   + 'RETURNS TABLE ' + Char(13) + Char(10) 

Proposed bug fix:
SET @SQL = 'CREATE FUNCTION ' + @SchemaName + '.' + @TableName + '_RowHistory (@PK __' + @PKDataType +'__)' + Char(13) + Char(10)
   + 'RETURNS TABLE ' + Char(13) + Char(10) 


I also changed the length of the PrimaryKey field in the Audit table to be 50, allows for longer representations of PK's, such as GUID's which are 36 chars.

Thanks for the great script!
Coordinator
Oct 31, 2013 at 4:57 PM
The latest release of AutoAudit 3.20e resolves issues associated with non-numeric PK's and handles composite primary keys of up to 5 columns.
The maximum size of each column is 36 characters to be capable of handling a guid.
Each key column of the composite PK is stored in its own column in the AuditHeader table in columns PrimaryKey, PrimaryKey2, PrimaryKey3, PrimaryKey4 and PrimaryKey5.
Marked as answer by JohnSigouin on 10/31/2013 at 9:57 AM