Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers ForumsOther TechnologiesDatabase Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
  #1  
Old November 8th, 2003, 12:29 AM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
 
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
Syntax Error?/Help

Windows Server 2003, MySQL 4.0.16-max-nt

Trying to execute the following command gives the error you see below;
mysql> if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[checkspam]') and OBJECTPROPERTY(id, N'IsProcedure') = 1);
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'if exists (select * from dbo.sysobjects where id = object_id(N'
mysql>

It seems to be cutting off my command. I have tried substituting " and ` for
the ' char, but they all seem to suffer the same problem. Ultimitely, I want
to use all of the following;
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[checkspam]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[checkspam]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[checkspam] AS
-- Stored Procedure to extract 550 errors from Microsoft IIS SMTP log
-- (C) 2003 Pieter Vuijk & Martijn Jongen

DECLARE @clienthost varchar(50),
@logtime datetime,
@txt_FROM varchar(250),
@txt_RCPT varchar(250),
@txt_UNKNOWN varchar(10)

--@txt_UNKNOWN must have at lease one character, space is allowed
set @txt_UNKNOWN='unknown'

IF EXISTS (SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE Table_Name
= 'Logging')
begin
DROP TABLE Logging
end

CREATE TABLE Logging
(
HOSTNAAM varchar(50),
Logtijd datetime,
mailFrom varchar(255),
mailRcpt varchar(255)
)

DECLARE Client_crs CURSOR FOR
SELECT clienthost, logtime
FROM inetlog WHERE servicestatus=550

OPEN Client_crs
FETCH NEXT FROM Client_crs INTO @clienthost, @logtime
WHILE @@FETCH_STATUS = 0
BEGIN

set @txt_FROM = @txt_UNKNOWN
set @txt_RCPT = @txt_UNKNOWN

SELECT @txt_FROM = REPLACE(Parameters, ' ','')
FROM inetlog
WHERE clienthost = @clienthost
AND DATEDIFF(ss, @logtime, logtime) BETWEEN -1 AND 1.5
AND Operation = 'MAIL'

SELECT @txt_RCPT = REPLACE(Parameters, ' ','')
FROM inetlog
WHERE clienthost = @clienthost
AND DATEDIFF(ss, @logtime, logtime) BETWEEN -1 AND 1.5
AND Operation = 'RCPT'

INSERT INTO Logging
SELECT REPLACE(@clienthost, ' ','') AS Hostnaam ,@Logtime ,
CASE WHEN CHARINDEX(@txt_UNKNOWN, @txt_FROM) >0 THEN @txt_UNKNOWN
else
substring(@txt_FROM,7,len(@txt_FROM)-7)
end
,
CASE WHEN CHARINDEX(@txt_UNKNOWN, @txt_RCPT) > 0 then @txt_UNKNOWN
else
substring(@txt_RCPT,5,len(@txt_RCPT)-5)
end
FETCH NEXT FROM Client_crs INTO @clienthost, @logtime
END
CLOSE Client_crs
DEALLOCATE Client_crs
SELECT * FROM Logging
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If you want to know what this is, go here;
http://www.martijnjongen.com/eng/html/log_analyzer.htm

Please email me directly at vgill@mailandnews.com.

Thank You!
Vern

Reply With Quote
  #2  
Old November 10th, 2003, 02:01 PM
xs0 xs0 is offline
Codewalkers Novice (500 - 999 posts)
 
Join Date: Apr 2007
Location: Ljubljana, Slovenia
Posts: 760 xs0 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 2
RE: Syntax Error?/Help

To me, it looks like you want to do a Microsoft SQL Server command on MySQL. Those are two different products and are not compatible...

Reply With Quote
Reply

Viewing: Codewalkers ForumsOther TechnologiesDatabase Help > Syntax Error?/Help


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway