?
Solved

SQL 2000: Audit Script - Syntax error

Posted on 2008-11-06
7
Medium Priority
?
241 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:VoodooFrog
  • 4
  • 2
7 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22897349
Wow ... Does the Select @SQL part happen?  If so, would you post the result?
0
 

Author Comment

by:VoodooFrog
ID: 22897395
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1000 total points
ID: 22897467
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 7

Assisted Solution

by:Cedric_D
Cedric_D earned 1000 total points
ID: 22897474
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
 

Author Comment

by:VoodooFrog
ID: 22897583
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
 

Author Comment

by:VoodooFrog
ID: 22897753
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
 

Author Closing Comment

by:VoodooFrog
ID: 31513993
Invisible character in code messed up compiler.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question