Hi,
I am creating an audit trail based on something found on sql server central and it is crashing on a particular table when generating the shadow table.
This is my table definition (it's got way too many columns for my liking, but I can't do anything about that at the moment)
USE [BPOptical]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cltrials](
[itrialid] [char](16) NOT NULL,
[iexamid] [char](16) NOT NULL,
[mnotesod] [text] NULL,
[mnotesos] [text] NULL,
[mnotes] [text] NULL,
[ibasecurveod] [char](16) NULL,
[ibasecurveos] [char](16) NULL,
[idiameterod] [char](16) NULL,
[idiameteros] [char](16) NULL,
[isphereod] [char](16) NULL,
[isphereos] [char](16) NULL,
[itypeidod] [char](16) NULL,
[itypeidos] [char](16) NULL,
[nkeradiaod1] [numeric](4, 2) NULL,
[nkeradiaos1] [numeric](4, 2) NULL,
[nkeradiaod2] [numeric](4, 2) NULL,
[nkeradiaos2] [numeric](4, 2) NULL,
[nkeradiaod3] [numeric](4, 2) NULL,
[nkeradiaos3] [numeric](4, 2) NULL,
[nkeraradod1] [numeric](4, 2) NULL,
[nkerarados1] [numeric](4, 2) NULL,
[nkeraradod2] [numeric](4, 2) NULL,
[nkerarados2] [numeric](4, 2) NULL,
[nkeraradod3] [numeric](4, 2) NULL,
[nkerarados3] [numeric](4, 2) NULL,
[ckerapowerod] [char](6) NULL,
[ckerapoweros] [char](6) NULL,
[cdesc] [varchar](50) NOT NULL,
[lfinal] [bit] NULL,
[icylod] [char](16) NULL,
[icylos] [char](16) NULL,
[iaxisod] [int] NULL,
[iaxisos] [int] NULL,
[iaddod] [char](16) NULL,
[iaddos] [char](16) NULL,
[ctintod] [char](20) NULL,
[ctintos] [char](20) NULL,
[imanuidod] [char](16) NULL,
[imanuidos] [char](16) NULL,
[ldailywearod] [bit] NULL,
[ldailywearos] [bit] NULL,
[lextendedwearod] [bit] NULL,
[lextendedwearos] [bit] NULL,
[lgaspermeableod] [bit] NULL,
[lgaspermeableos] [bit] NULL,
[ldisposableod] [bit] NULL,
[ldisposableos] [bit] NULL,
[ltoricod] [bit] NULL,
[ltoricos] [bit] NULL,
[clt_date] [date] NULL,
[clt_type] [nchar](3) NULL,
[clt_solutions] [text] NULL,
[clt_otherrxod] [varchar](50) NULL,
[clt_otherrxos] [varchar](50) NULL,
[clt_wlfitod] [text] NULL,
[clt_wlfitos] [text] NULL,
[clt_flfitod] [text] NULL,
[clt_flfitos] [text] NULL,
[clt_vaod] [varchar](50) NULL,
[clt_vaos] [varchar](50) NULL,
[clt_orod] [varchar](50) NULL,
[clt_oros] [varchar](50) NULL,
[clt_orvaod] [varchar](50) NULL,
[clt_orvaos] [varchar](50) NULL,
[clt_finalrxod] [varchar](50) NULL,
[clt_finalrxos] [varchar](50) NULL,
[clt_rxnotesod] [text] NULL,
[clt_rxnotesos] [text] NULL,
[clt_finalrxnotesod] [text] NULL,
[clt_finalrxnotesos] [text] NULL,
[clt_action] [text] NULL,
[clt_aftercarecornod] [text] NULL,
[clt_aftercarecornos] [text] NULL,
[clt_aftercaredate] [date] NULL,
[clt_aftercarecornstainod] [text] NULL,
[clt_aftercarecornstainos] [text] NULL,
[clt_aftercareconjstainod] [text] NULL,
[clt_aftercareconjstainos] [text] NULL,
[clt_aftercareaction] [text] NULL,
[clt_multifocalod] [text] NULL,
[clt_multifocalos] [text] NULL,
[clt_vadistod] [varchar](50) NULL,
[clt_vadistos] [varchar](50) NULL,
[clt_vanearod] [varchar](50) NULL,
[clt_vanearos] [varchar](50) NULL,
[clt_vabin] [varchar](50) NULL,
[clt_orvabin] [varchar](50) NULL,
[clt_wtt] [text] NULL,
[clt_lwt] [text] NULL,
[clt_aftercareconjinjod] [text] NULL,
[clt_aftercareconjinjos] [text] NULL,
[clt_aftercarelashesod] [text] NULL,
[clt_aftercarelashesos] [text] NULL,
[clt_aftercarePAPod] [text] NULL,
[clt_aftercarePAPos] [text] NULL,
[clt_aftercareTBUTod] [text] NULL,
[clt_aftercareTBUTos] [text] NULL,
[clt_rgpcentrationod] [text] NULL,
[clt_rgpcentrationos] [text] NULL,
[clt_rgpmovementod] [text] NULL,
[clt_rgpmovementos] [text] NULL,
[clt_rgpFLFitod] [text] NULL,
[clt_rgpFLFitos] [text] NULL,
[clt_slitlampbaseline] [varchar](50) NULL,
[clt_coverageod] [char](16) NULL,
[clt_coverageos] [char](16) NULL,
[clt_centrationod] [char](16) NULL,
[clt_centrationos] [char](16) NULL,
[clt_movementod] [char](16) NULL,
[clt_movementos] [char](16) NULL,
[clt_rotationangleod] [char](16) NULL,
[clt_rotationangleos] [char](16) NULL,
[clt_rotationod] [char](16) NULL,
[clt_rotationos] [char](16) NULL,
[clt_vabinnear] [varchar](50) NULL,
[clt_vabindist] [varchar](50) NULL,
[clt_orsphereod] [char](16) NULL,
[clt_orsphereos] [char](16) NULL,
[clt_orcylinderod] [char](16) NULL,
[clt_orcylinderos] [char](16) NULL,
[clt_ordiameterod] [char](16) NULL,
[clt_ordiameteros] [char](16) NULL,
[clt_oraxisod] [int] NULL,
[clt_oraxisos] [int] NULL,
[clt_oraddod] [char](16) NULL,
[clt_oraddos] [char](16) NULL,
[clt_orvabinnear] [varchar](50) NULL,
[clt_orvabindist] [varchar](50) NULL,
[clt_orvanearod] [varchar](50) NULL,
[clt_orvanearos] [varchar](50) NULL,
[clt_orvadistod] [varchar](50) NULL,
[clt_orvadistos] [varchar](50) NULL,
[clt_finalsphereod] [char](16) NULL,
[clt_finalsphereos] [char](16) NULL,
[clt_finalcylinderod] [char](16) NULL,
[clt_finalcylinderos] [char](16) NULL,
[clt_finalaxisod] [int] NULL,
[clt_finalaxisos] [int] NULL,
[clt_finaladdod] [char](16) NULL,
[clt_finaladdos] [char](16) NULL,
[clt_pushupod] [char](16) NULL,
[clt_pushupos] [char](16) NULL,
[clt_rgpotherod] [varchar](50) NULL,
[clt_rgpotheros] [varchar](50) NULL,
[usr_fk] [uniqueidentifier] NULL,
CONSTRAINT [PK_cltrials] PRIMARY KEY NONCLUSTERED
(
[itrialid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[cltrials] WITH CHECK ADD CONSTRAINT [FK_cltrials_exams] FOREIGN KEY([iexamid])
REFERENCES [dbo].[exams] ([iexamid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[cltrials] CHECK CONSTRAINT [FK_cltrials_exams]
GO
This is the stored procedure that I use to generate the shadow table:
CREATE PROCEDURE [dbo].[GenerateAudittrail]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtention varchar(128) = '_shadow',
@DropAuditTable bit = 0
AS
BEGIN
IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END
IF @AuditNameExtention is null
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC ('drop table ' + @TableName + @AuditNameExtention)
END
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId
OPEN TableColumns
DECLARE @ColumnName varchar(128)
DECLARE @ColumnType varchar(128)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint
DECLARE @CreateStatement varchar(8000)
DECLARE @ListOfFields varchar(2000)
SET @ListOfFields = ''
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table already exists. Only triggers will be updated.'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
END
ELSE
BEGIN
SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ('
SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
END
IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '
IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + 'NOT '
SET @CreateStatement = @CreateStatement + 'NULL, '
END
FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END
SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC (@CreateStatement)
SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'
EXEC (@CreateStatement)
END
CLOSE TableColumns
DEALLOCATE TableColumns
/* Drop Triggers, if they exist */
PRINT 'Dropping triggers'
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')
/* Create triggers */
PRINT 'Creating triggers'
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted')
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')
ENDI run the SP like this:
EXECUTE GenerateAudittrail 'CLTrials'
The code that gets generated to create the shadow table has this in it:
CREATE TABLE [dbo].[cltrials_shadow] (... lots of other fields here..., [clt_rotationos] [char] (16) COLLATE Latin1_General_CI_AS NULL, [clt_vabinnear] [varchar] (50) COLLATENULL,
For some reason the "COLLATENULL" is generated rather than "COLLATE Latin1_General_CI_AS NULL" but I can't see why. This is my first time ever trying to debug code in SQL Server so any help appreciated.
