You can always declare your variables to be varchar(max) (which I did, BTW).

Add print statement for all current variables so we can see them. For some reason the collation is set to empty string. Also, what happens when you run that statement (the one used for cursor) for the table in question?

>Thanks Naomi, please explain why? The code runs fine on other tables. I'm wondering if the problem is the size of the table and hence the @createstatement variable can't hold the entire string.
>
>FWIW, those changes had no effect, but thanks anyhow.
>
>>Move DECLARE statements outside the DECLARE CURSOR statement, e.g.
>>
>>

>>-- Declare temp variable to fetch records into
>>>	DECLARE @ColumnName varchar(128)
>>>	DECLARE @ColumnType varchar(128)
>>>	DECLARE @ColumnLength smallint
>>>	DECLARE @ColumnNullable int
>>>	DECLARE @ColumnCollation sysname
>>>	DECLARE @ColumnPrecision tinyint
>>>	DECLARE @ColumnScale tinyint
>>>
>>>	-- Declare variable to build statements
>>>	DECLARE @CreateStatement varchar(max)
>>>	DECLARE @ListOfFields varchar(max)
>>>	SET @ListOfFields = ''
>>>

>>
>>Move that code before DECLARE cursor.
>>
>>Also, add LOCAL FAST_FORWARD statements to cursor definition.
>>
>>>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
>>>
>>>/****** Object:  Table [dbo].[cltrials]    Script Date: 08/09/2015 11:00:22 AM ******/
>>>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
>>>
>>>	-- Check if table exists
>>>	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
>>>
>>>	-- Check @AuditNameExtention
>>>	IF @AuditNameExtention is null
>>>	BEGIN
>>>		PRINT 'ERROR: @AuditNameExtention cannot be null'
>>>		RETURN
>>>	END
>>>
>>>	-- Drop audit table if it exists and drop should be forced
>>>	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 cursor to loop over columns
>>>	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 temp variable to fetch records into
>>>	DECLARE @ColumnName varchar(128)
>>>	DECLARE @ColumnType varchar(128)
>>>	DECLARE @ColumnLength smallint
>>>	DECLARE @ColumnNullable int
>>>	DECLARE @ColumnCollation sysname
>>>	DECLARE @ColumnPrecision tinyint
>>>	DECLARE @ColumnScale tinyint
>>>
>>>	-- Declare variable to build statements
>>>	DECLARE @CreateStatement varchar(8000)
>>>	DECLARE @ListOfFields varchar(2000)
>>>	SET @ListOfFields = ''
>>>
>>>
>>>	-- Check if audit table exists
>>>	IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
>>>	BEGIN
>>>		-- AuditTable exists, update needed
>>>		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
>>>		-- AuditTable does not exist, create new
>>>
>>>		-- Start of create table
>>>		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
>>>		
>>>		-- Add audit trail columns
>>>		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)' 
>>>
>>>		-- Create audit table
>>>		PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
>>>		EXEC (@CreateStatement)
>>>
>>>		-- Set primary key and default values
>>>		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')
>>>
>>>END
>>>

>>>
>>>I 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.