SQL 2000: Audit Script - Syntax error

This script was adapted from a script similar to many others that I have seen. This one reads in a list of tables from a designated table. It then loops through the list and creates the trigger for each table in the list. However when I go to execute this script I get the following error that occurs when the code to create the trigger runs.

Msg 170, Level 15, State 1, Procedure MyTable_ChangeTracking, Line 57
Line 57: Incorrect syntax near ' '.

It acutally occurs on line 122, "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,"

Any ideas why I am getting this error?
Thanks!
USE [MyDbName]
GO
 
IF NOT EXISTS	
	(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[TableAudit]')
             AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE TableAudit
(
	TableAudit_ID [int]IDENTITY(1,1) NOT NULL,
	Type char(1), 
	TableName varchar(128), 
	PrimaryKeyField varchar(1000), 
	PrimaryKeyValue varchar(1000), 
	FieldName varchar(128), 
	OldValue varchar(1000), 
	NewValue varchar(1000), 
	UpdateDate datetime DEFAULT (GetDate()), 
	UserName varchar(128)
)
GO
 
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
 
SELECT @TABLE_NAME = MIN(NAME) FROM SysObjects 
WHERE Name != 'sysdiagrams'
AND Name != 'TableAudit'
AND xtype='U'
	
WHILE @TABLE_NAME IS NOT NULL
BEGIN
	--EXEC('IF EXISTS (SELECT * FROM sysobjects WHERE Name=''' + @TABLE_NAME + '_ChangeTracking'' AND xtype = ''TR'')  DROP TRIGGER ' + @TABLE_NAME + '_ChangeTracking')	
	IF EXISTS 
		(SELECT * FROM sysobjects 
		WHERE Name= @TABLE_NAME + '_ChangeTracking' 
		AND xtype = 'TR')  
	EXEC('DROP TRIGGER ' + @TABLE_NAME  + '_ChangeTracking')
 
	SELECT @TABLE_NAME = MIN(NAME) FROM SysObjects 
	WHERE NAME > @TABLE_NAME
	AND Name != 'sysdiagrams'
	AND Name != 'TableAudit'
	AND xtype='U'
END
 
DECLARE @TableList TABLE
(
	RowNum int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	TableName nvarchar(255)
)
DECLARE @RowCnt int
DECLARE @MaxRows int
 
INSERT INTO @TableList (TableName)
SELECT TableAuditList_Name 
FROM TableAuditList
 
SELECT @MaxRows = COUNT(*) FROM @TableList
SET @RowCnt=1
 
WHILE @RowCnt <= @MaxRows
BEGIN
	SELECT @Table_Name = TableName FROM @TableList WHERE RowNum = @RowCnt
	
	SELECT @sql = 
	'
	create trigger ' + @TABLE_NAME + '_ChangeTracking on ' + @TABLE_NAME + ' for insert, update, delete
	as
 
	declare @bit int ,
	@field int ,
	@maxfield int ,
	@char int ,
	@fieldname varchar(128) ,
	@TableName varchar(128) ,
	@PKCols varchar(1000) ,
	@sql varchar(2000), 
	@UpdateDate varchar(21) ,
	@UserName varchar(128) ,
	@Type char(1) ,
	@PKFieldSelect varchar(1000),
	@PKValueSelect varchar(1000)
 
	select @TableName = ''' + @TABLE_NAME + '''
 
	-- date and user
	select @UserName = system_user,
	@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
 
	-- Action
	if exists (select * from inserted)
	if exists (select * from deleted)
	select @Type = ''U''
	else
	select @Type = ''I''
	else
	select @Type = ''D''
 
	-- get list of columns
	select * into #ins from inserted
	select * into #del from deleted
 
	-- Get primary key columns for full outer join
	select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
	from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	where pk.TABLE_NAME = @TableName
	and CONSTRAINT_TYPE = ''PRIMARY KEY''
	and c.TABLE_NAME = pk.TABLE_NAME
	and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
	-- Get primary key fields select for insert
	select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' 
	from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
	where pk.TABLE_NAME = @TableName
	and CONSTRAINT_TYPE = ''PRIMARY KEY''
	and c.TABLE_NAME = pk.TABLE_NAME
	and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
	select @PKValueSelect = coalesce(@PKValueSelect + ''+'', '''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
	from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,    
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
	where  pk.TABLE_NAME = @TableName   
	and CONSTRAINT_TYPE = ''PRIMARY KEY''   
	and c.TABLE_NAME = pk.TABLE_NAME   
	and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
 
	if @PKCols is null
	begin
	raiserror(''no PK on table %s'', 16, -1, @TableName)
	return
	end
 
	select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
	while @field < @maxfield
	begin
	select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
	select @bit = (@field - 1 )% 8 + 1
	select @bit = power(2,@bit - 1)
	select @char = ((@field - 1) / 8) + 1
	if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
	begin
	select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
	select @sql = ''insert TableAudit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
	select @sql = @sql + '' select '''''' + @Type + ''''''''
	select @sql = @sql + '','''''' + @TableName + ''''''''
	select @sql = @sql + '','' + @PKFieldSelect
	select @sql = @sql + '','' + @PKValueSelect
	select @sql = @sql + '','''''' + @fieldname + ''''''''
	select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
	select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
	select @sql = @sql + '','''''' + @UpdateDate + ''''''''
	select @sql = @sql + '','''''' + @UserName + ''''''''
	select @sql = @sql + '' from #ins i full outer join #del d''
	select @sql = @sql + @PKCols
	select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 
	select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)'' 
	select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)'' 
	exec (@sql)
	end
	end
	'
	SELECT @sql
	EXEC(@sql)
 
	SET @RowCnt = @RowCnt + 1	
END

Open in new window

VoodooFrogAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
Wow ... Does the Select @SQL part happen?  If so, would you post the result?
0
VoodooFrogAuthor Commented:
The result is the error code listed above. That is why the Line Number listed in the error message is not the actual line number in the code snippet.
Here is the message again:
Msg 170, Level 15, State 1, Procedure MyTable_ChangeTracking, Line 57
Line 57: Incorrect syntax near ' '.
0
Daniel WilsonCommented:
OK, so it is creating the SQL statement & attempting to execute it ...

Please remove the EXEC statement so we can just see the resulting SQL.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Cedric_DCommented:
I tried and for me it parsed well..

So, you have to insert PRINT @SQL before EXEC(@SQL).

Copy this debug string from Results pane to query pane, re-execute and you will see exact error.
0
VoodooFrogAuthor Commented:
Ok, here is the SQL that gets executed:
create trigger MyTable_ChangeTracking on MyTable for insert, update, delete
as
 
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000), 
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
 
select @TableName = 'MyTable'
 
-- date and user
select @UserName = system_user,
@UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
 
-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = 'U'
else
select @Type = 'I'
else
select @Type = 'D'
 
-- get list of columns
select * into #ins from inserted
select * into #del from deleted
 
-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+'+','') + '''' + COLUMN_NAME + '''' 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
 
select @PKValueSelect = coalesce(@PKValueSelect + '+', '') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,    
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c   
where  pk.TABLE_NAME = @TableName   
and CONSTRAINT_TYPE = 'PRIMARY KEY'   
and c.TABLE_NAME = pk.TABLE_NAME   
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
 
if @PKCols is null
begin
raiserror('no PK on table %s', 16, -1, @TableName)
return
end
 
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = 'insert TableAudit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql + ' select ''' + @Type + ''''
select @sql = @sql + ',''' + @TableName + ''''
select @sql = @sql + ',' + @PKFieldSelect
select @sql = @sql + ',' + @PKValueSelect
select @sql = @sql + ',''' + @fieldname + ''''
select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
select @sql = @sql + ',''' + @UpdateDate + ''''
select @sql = @sql + ',''' + @UserName + ''''
select @sql = @sql + ' from #ins i full outer join #del d'
select @sql = @sql + @PKCols
select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname 
select @sql = @sql + ' or (i.' + @fieldname + ' is null and  d.' + @fieldname + ' is not null)' 
select @sql = @sql + ' or (i.' + @fieldname + ' is not null and  d.' + @fieldname + ' is null)' 
exec (@sql)
end
end

Open in new window

0
VoodooFrogAuthor Commented:
omg... Ok thank you guys for your help... there must have been a screwey character in there caused by pasting from application to application. The code is fine visually. Retyped the exact same code out and it works now.

Thank you again!
0
VoodooFrogAuthor Commented:
Invisible character in code messed up compiler.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.