LogSat Software

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I'm trying to move the current Access DB to Microsoft SQL Server 7.0.  However, when I try to execute the following statement from the .sql file provided, I receive the error "Incorrect syntax near the keyword 'ON'."  
ALTER TABLE [dbo].[tblQuarantine] ADD 
 CONSTRAINT [FK_tblQuarantine_tblMsgs] FOREIGN KEY 
 (
  [MsgID]
 ) REFERENCES [dbo].[tblMsgs] (
  [MsgID]
 ) ON DELETE CASCADE  NOT FOR REPLICATION 

Any ideas?
I tried this on a SQL Server 2000 and it worked just fine.

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
Brice Beyer Members Profile Send Private Message Find Members Posts Add to Buddy List Guest Group	Post Options Post Reply Quote Brice Beyer Thanks(0) Quote Reply Topic: MS SQL Quarantine DB Posted: 20 August 2004 at 10:20am
	I'm trying to move the current Access DB to Microsoft SQL Server 7.0. However, when I try to execute the following statement from the .sql file provided, I receive the error "Incorrect syntax near the keyword 'ON'." ALTER TABLE [dbo].[tblQuarantine] ADD CONSTRAINT [FK_tblQuarantine_tblMsgs] FOREIGN KEY ( [MsgID] ) REFERENCES [dbo].[tblMsgs] ( [MsgID] ) ON DELETE CASCADE NOT FOR REPLICATION Any ideas? I tried this on a SQL Server 2000 and it worked just fine.

LogSat Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group Joined: 25 January 2005 Location: United States Status: Offline Points: 4106	Post Options Post Reply Quote LogSat Report Post Thanks(0) Quote Reply Posted: 20 August 2004 at 5:37pm
	Brice, Are you running the scripts manually using a SQL query tool (if so, which one?), or are you trying to setup the database using SpamFilter's 1-2-3 step wizard? In either case, can you please provide the complete text of the query result errors? Roberto F. LogSat Software

Brice Beyer Members Profile Send Private Message Find Members Posts Add to Buddy List Guest Group	Post Options Post Reply Quote Brice Beyer Thanks(0) Quote Reply Posted: 23 August 2004 at 4:06pm
	Using SQL Query Analyzer to execute the statement. Error returned is: Server: Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'ON'. My understanding is that SQL Server 7.0 does not support the ON DELETE functionality.

LogSat Members Profile Send Private Message Find Members Posts Add to Buddy List Admin Group Joined: 25 January 2005 Location: United States Status: Offline Points: 4106	Post Options Post Reply Quote LogSat Report Post Thanks(0) Quote Reply Posted: 24 August 2004 at 12:10am
	Brice, We were able to duplicate the problem. As you correctly stated, SQL 7 does not support the ON DELETE CASCADE statement. SpamFilter does not require the cascading option to be present, as the code will perform the necessary cleanup if the database does not do it. You should be able to modify the MicrosoftSQL.sql file by removing the whole ALTER statement below. ALTER TABLE [dbo].[tblQuarantine] ADD CONSTRAINT [FK_tblQuarantine_tblMsgs] FOREIGN KEY ( [MsgID] ) REFERENCES [dbo].[tblMsgs] ( [MsgID] ) ON DELETE CASCADE NOT FOR REPLICATION If that foreign key is already present in the database, please remove it by running the following sql script: ALTER TABLE [dbo].[tblQuarantine] DROP CONSTRAINT FK_tblQuarantine_tblMsgs or by using the SQL Manager GUI to remove the constraint. Please let us know if you need further help in these procedures. Roberto F. LogSat Software Please let us know if you still encounter any problems.

LogSat Software

Site Navigation[Skip]

Spam Filter ISP Support Forum

MS SQL Quarantine DB