Subject: RE: [Emerald] Upgrade Date: Thu, 11 Dec 2003 09:23:08 -0500 Message-ID: <45B39D99518DEF49AEBBD4769E886D2D02E1C0@server.studioforpublications.com> From: "John Lord" <lord@allturbo.com>
I havent put this upgrade on yet , I think Im waiting until after
christms . But in the past I just would copy the store procedure
VerifyMailUser and paste it into notepad then after the upgrade I would
past it back in , When I first upgraded from 2.5 my mailsite broke and
support redid it for me and since then every upgrade breaks it so that's
how I get around it.
Eric here is what mine is if you want to compare it against what you
have now after the upgrade
CREATE PROCEDURE dbo.VerifyMailUser @username varchar(40), @domain
varchar(40), @esid integer AS
CREATE TABLE #AccountIDs (AccountID INT)
INSERT INTO #AccountIDs
SELECT AccountID FROM SubAccounts WHERE Email = @username + '@' +
@domain
UNION
SELECT AccountID FROM SubAccounts sa, Domains d WHERE sa.Login =
@username AND sa.DomainID = d.DomainID AND d.MailDomain = @domain
DELETE #AccountIDs
FROM #AccountIDs ai, SubAccounts sa, MasterAccounts ma
WHERE ai.AccountID = sa.AccountID
AND sa.CustomerID = ma.CustomerID
AND (sa.Active <> 1 OR ma.Active <> 1)
IF (SELECT COUNT(AccountID) FROM #AccountIDs) = 0
INSERT INTO #AccountIDs
SELECT AccountID FROM Aliases WHERE Alias = @username + '@' + @domain
SELECT Login, Login AS Shell, Email,@domain AS MailDomain,
CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit > (ma.Balance +
ma.PendingDebit - ma.PendingCredit))
OR (sa.ExpireDate IS NOT NULL AND DateAdd(Day,
(sa.Extension+COALESCE(ma.OverDue,0)+1), sa.ExpireDate) < GetDate())
THEN RIGHT(CONVERT(VARCHAR(20),RAND()),4) +
RIGHT(CONVERT(VARCHAR(20),RAND()),4)
ELSE sa.Password END AS Password, HomeDir
From MasterAccounts ma, SubAccounts sa, AccountTypes at1,
#AccountIDs ai
WHERE ma.CustomerID = sa.CustomerID
AND sa.AccountID = ai.AccountID
AND at1.AccountTypeID = sa.AccountTypeID
AND at1.ExternalSystemID = @esid
AND (ma.CancelDate IS NULL OR ma.CancelDate >=
getdate())
AND ma.StartDate < GETDATE()
ORDER BY Email DESC
DROP TABLE #AccountIDs
GO
John Lord
It Manager
AllTurbo Internet Services Inc
410-213-9388 Office
www.allturbo.com
-----Original Message-----
From: Jeffrey M Donley [mailto:jeffd@lmf.net]
Sent: Thursday, December 11, 2003 7:50 AM
To: emerald@iea-software.com
Subject: RE: [Emerald] Upgrade
Hi,
Just wondering if this was the fix for your mailsite troubles. I was
going to do the upgrade but curious if the mailsite problem has been
resolved.
Jeff
-----Original Message-----
From: emerald-request@iea-software.com
[mailto:emerald-request@iea-software.com]On Behalf Of John Lord
Sent: Tuesday, December 09, 2003 6:33 PM
To: emerald@iea-software.com
Subject: RE: [Emerald] Upgrade
Everytime I do an upgrade my mail site quits working , so what I have
been doing is copying the stored procedures for mailsite and then
pasting them back in after the upgrade.
John Lord
It Manager
AllTurbo Internet Services Inc
410-213-9388 Office
www.allturbo.com
-----Original Message-----
From: Hall, Eric M. [mailto:ehall@amaxx.com]
Sent: Tuesday, December 09, 2003 5:09 PM
To: emerald@iea-software.com
Subject: [Emerald] Upgrade
Has anyone did the upgrade that is using Mailsite ? If so did you have a
problem with Mailsite and the database plug-in?
After the upgrade my mailsite gives me a error # 2 can't find file.
Any help would be great
CREATE PROCEDURE dbo.VerifyMailUser @username varchar(40), @domain varchar(40), @esid integer AS
CREATE TABLE #AccountIDs (AccountID INT)
INSERT INTO #AccountIDs
SELECT AccountID FROM SubAccounts WHERE Email = @username + '@' + @domain
UNION
SELECT AccountID FROM SubAccounts sa, Domains d WHERE sa.Login = @username AND sa.DomainID = d.DomainID AND d.MailDomain = @domain
DELETE #AccountIDs
FROM #AccountIDs ai, SubAccounts sa, MasterAccounts ma
WHERE ai.AccountID = sa.AccountID
AND sa.CustomerID = ma.CustomerID
AND (sa.Active <> 1 OR ma.Active <> 1)
IF (SELECT COUNT(AccountID) FROM #AccountIDs) = 0
INSERT INTO #AccountIDs
SELECT AccountID FROM Aliases WHERE Alias = @username + '@' + @domain
SELECT Login, Login AS Shell, Email,@domain AS MailDomain,
CASE WHEN (ma.OverLimit > 0 AND ma.OverLimit > (ma.Balance + ma.PendingDebit - ma.PendingCredit))
OR (sa.ExpireDate IS NOT NULL AND DateAdd(Day, (sa.Extension+COALESCE(ma.OverDue,0)+1), sa.ExpireDate) < GetDate())
THEN RIGHT(CONVERT(VARCHAR(20),RAND()),4) + RIGHT(CONVERT(VARCHAR(20),RAND()),4)
ELSE sa.Password END AS Password, HomeDir
From MasterAccounts ma, SubAccounts sa, AccountTypes at1, #AccountIDs ai
WHERE ma.CustomerID = sa.CustomerID
AND sa.AccountID = ai.AccountID
AND at1.AccountTypeID = sa.AccountTypeID
AND at1.ExternalSystemID = @esid
AND (ma.CancelDate IS NULL OR ma.CancelDate >= getdate())
AND ma.StartDate < GETDATE()
ORDER BY Email DESC
DROP TABLE #AccountIDs
GO