SpamSafe for Exchange / SMTP Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SpamSafe for Exchange / SMTP Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-07 : 07:52:07

Brian writes "I have written an Anti-Spam System for SMTP / Exchange 2000 and I am busy trying to convert all the Client SQL to Server SQL.
I have a procedure that I am stumped with.

I pass 4 Variables to the Procedure and the following SHOULD happen.

If @MsgGUID does not exist in the MESSAGES Table then the it should be INSERTed, if it does exist then it should be updated.
@Tokens is a delimited String which is parsed to the Function and each element of the array is checked for existence in the TOKENS Table. If the Token does exist then id @IsSpam is TRUE then the TOKENS.Spams Column is incremented by ONE else the TOKENS.Hams Column is incremented by ONE. If the Token does NOT exist then it is added to the TOKENS Table and the appropriate Column is set to ONE or ZERO (Spams / Hams)
Finally the Score is calculated and written to the TOKENS.Score Column.

I know this is a big ask but I really need this functionality and I am a NOVICE at SQL.

The 2 Tables have the following Columns

Messages
------------
MsgGUID nVarChar(32) - I allocate this GUID Manually and is the Key
Spam bit(1) True if Spam or False if not Spam

Tokens
---------
Token nVarChar(50) - Key Column
Spams Int(4)
Hams Int(4)
Score decimal(5)

I get the following error when trying to Check this Procedures Syntax
Error 446: Cannot resolve collation conflict for equal operation.
-- --------------------------------------------------
-- CREATE PROCEDURE UPDATE TOKENS (by mwesch)
-- --------------------------------------------------
CREATE PROCEDURE spUpdateTokens
(
@MsgGUID nVarChar(32),
@Tokens nVarChar(4000),
@Delimiter Char(1),
@IsSpam Bit
)
AS
SET NoCount ON
SET Xact_Abort ON
DECLARE @GoodMessages int,
@BadMessages int
BEGIN TRANSACTION
If @IsSpam = 1
BEGIN
UPDATE Messages
SET Spam = 1
WHERE MsgGUID = @MsgGUID

UPDATE T
SET T.Spams = T.Spams + 1
FROM Tokens T
INNER JOIN fnSplitString(@Tokens, @Delimiter) F
ON T.Token = F.Token
/*
INSERT Tokens (Token, Spams)
SELECT F.Token, 1
FROM fnSplitString(@Tokens, @Delimiter) F
LEFT OUTER JOIN Tokens T
ON F.Token = T.Token
WHERE T.Token is null
*/
END

/* If @IsSpam = 0
BEGIN
UPDATE Messages
SET Spam = 0
WHERE MsgGUID = @MsgGUID

UPDATE T
SET T.Hams = T.Hams + 1
FROM Tokens T
INNER JOIN fnSplitString(@Tokens, @Delimiter) F
ON T.Token = F.Token

INSERT Tokens (Token, Hams)
SELECT F.Token, 1
FROM fnSplitString(@Tokens, @Delimiter) F
LEFT OUTER JOIN Tokens T
ON F.Token = T.Token
WHERE T.Token is null

END
*/
SELECT @GoodMessages = COUNT(*)
FROM Messages
WHERE Spam = 0

SELECT @BadMessages = COUNT(*)
FROM Messages
WHERE Spam = 1

If (@GoodMessages > 0 and @Badmessages > 0)
BEGIN
UPDATE Tokens
SET Score = Convert(decimal(6,3), Spams) / Convert(decimal(6,3), @BadMessages) /
(
Convert(decimal(6,3), Hams) / Convert(decimal(6,3), @GoodMessages) +
Convert(decimal(6,3), Spams) / Convert(decimal(6,3), @BadMessages)
)
END
COMMIT
GO

This function appears to work perfectly (Written by mwesch)
-- ---------------------------------------------------
-- SPLIT FUNCTION - Splits a String using a Delimiter
-- ---------------------------------------------------
CREATE FUNCTION fnSplitString
(
@InString nvarchar(4000),
@Delimiter char(1)
)

RETURNS @Tokens table
(
Token nVarChar(50)
)
AS
BEGIN

DECLARE @Position int

WHILE Len(@InString) > 0
BEGIN
SET @Position = CharIndex(@Delimiter, @InString)
If @Position > 0
BEGIN
INSERT @Tokens
SELECT LTrim(RTrim(Left(@InString, @Position - 1)))
SET @I

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 12:33:00

Well, what collations are you using for the columns and also for the database? Take a look at collations in SQL Server Books Online.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources