Print Page | Close Window

MS SQL Quarantine DB

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=4161
Printed Date: 02 June 2025 at 11:54am


Topic: MS SQL Quarantine DB
Posted By: Guests
Subject: MS SQL Quarantine DB
Date 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.


Replies:
Posted By: LogSat
Date 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

Posted By: Guests
Date 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.

Posted By: LogSat
Date 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.


Print Page | Close Window