I unfortunately have ntext fields in the table(s) that need to be audited and I've been trying to find a way to get around it.

My attempt (I can't call it a solution because it doesn't work) is to try to just insert the fields that aren't ntext, text or image into a temp table. I do this by getting the names of the fields for the table being audited from the syscolumns table and put them in a variable.

My code looks like this:

CREATE TABLE #TableCache (

columnName varchar(400)

)

INSERT INTO #TableCache

SELECT '[' + sc.name + ']' as columnName

FROM sysobjects as so JOIN syscolumns as sc

ON so.id = sc.id

WHERE so.xtype = 'U' AND sc.xtype NOT IN ( 99, 35, 34) AND so.name = @TableName

AND sc.name not in ('Created', 'Modified', 'RowVersion')

SET @FieldList = ''

-- create a cursor

DECLARE tableColumn CURSOR FOR

SELECT *

FROM #TableCache

-- loop over the cursor

-- and get a list of the columns in the table that are not ntext, text or image

OPEN tableColumn

FETCH NEXT FROM tableColumn INTO @columnName

WHILE @@FETCH_STATUS = 0

BEGIN

if len(@FieldList) = 0

begin

SET @FieldList = '[' + @columnName + ']'

end

else

begin

SET @FieldList = @FieldList + ', [' + @ColumnName + ']'

end

-- next row, please

FETCH NEXT FROM tableColumn INTO @columnName

END

print @FieldList

-- get list of columns

Set @SQL ='SELECT ' + @FieldList + ' INTO #ins FROM inserted'

EXEC (@SQL)

The trigger errors on the last line stating invalid object inserted. (NOTE: This code is inside a trigger)

If I try to use this:

SELECT @FieldList INTO #ins FROM inserted

I get No column was specified for column 1 of '#ins'.

Any ideas?