AW: [Emerald] Emerald 2.5.288 Admin problem.

Harald Logar ( HLogar@net4you.net )
Fri, 30 Jul 1999 15:45:08 +0200

Hi!

On creating a new Emerald-Database (Admin version 2.5.288) this error
occured:

"SQL Server Error: 1776 There are no primary or candidate keys in the
referenced table 'ServerPorts' that match the referencing column list =
in the
foreign key 'FK_ServerAcc_Serve_145C0A3F'
State=3D0, Severity=3D16"

When this error occures the script is not able to create the table
"serveraccess".

I found out that the error occurs at this statement in the insttabs.sql =
-
Script:

CREATE TABLE dbo.ServerAccess (
ServerID int NOT NULL ,
Port int NOT NULL ,
AccountType varchar (15) NOT NULL ,
StartTime int NULL DEFAULT (0),
StopTime int NULL DEFAULT (0),
MaxSessionLength int NULL DEFAULT (0),
CONSTRAINT pk_ServerAccess PRIMARY KEY CLUSTERED
(ServerID,Port,AccountType),
FOREIGN KEY (AccountType) REFERENCES dbo.AccountTypes (AccountType),
FOREIGN KEY (ServerID) REFERENCES dbo.ServerPorts (ServerID)
)
GO

Then I checked out the old Script-Files (2.5.261) and found a =
difference in
the last line.
FOREIGN KEY (ServerID,Port) REFERENCES dbo.ServerPorts
(ServerID,Port)

I corrected the line and the error didn=B4t occur again.

The second error I got was in the following statement:

-----------------------
CREATE PROCEDURE IMailUsers @esid INT, @username varchar(32), @domain
varchar(64) AS
SELECT sa.AccountID, CASE WHEN Email <> '' THEN

CASE WHEN sa.Email LIKE '%_@_%' THEN
SUBSTRING(sa.Email,1,CHARINDEX('@',sa.Email)-1) ELSE sa.Email END WHEN
sa.Login <> '' THEN sa.Login WHEN sa.Shell <> '' THEN sa.Shell ELSE
@username END AS Login

, sa.Password, sa.FirstName + ' ' + sa.LastName AS Name, imc.UserDir,
CASE WHEN sa.Email LIKE '%_@_%' THEN sa.Email WHEN sa.Email <> '' THEN
sa.Email + '@' + g.mailDomain WHEN
sa.Login <> '' THEN sa.Login + '@' + g.mailDomain WHEN sa.Shell <> '' =
THEN
sa.Shell + '@' + g.mailDomain END
AS MailAddress,
CASE WHEN imc.MaxSize IS NULL THEN 0 ELSE imc.MaxSize END AS MaxSize,
CASE WHEN imc.MaxMessages IS NULL THEN 0 ELSE imc.MaxMessages END AS
MaxMessages,
CASE WHEN imc.Type IS NULL THEN 0 ELSE imc.Type END AS Type,
CASE WHEN imc.Flags IS NULL THEN 128 ELSE imc.Flags END AS Flags
FROM SubAccounts sa, MasterAccounts ma, Groups g, Domains d, =
IMailConfigs
imc, AccountTypes at
WHERE sa.CustomerID =3D ma.CustomerID
AND ma.GroupID =3D g.GroupID
AND g.DomainID =3D d.DomainID
AND sa.AccountID *=3D imc.AccountID
AND sa.Password <> ''
AND (sa.Shell =3D @username OR sa.Login =3D @username OR sa.Email =3D
@username + '@' + @domain)
AND (g.MailDomain =3D @domain OR sa.Email =3D @username + '@' + =
@domain)
AND at.AccountType =3D sa.AccountType
AND at.ExternalSystemID =3D @esid
----------------------------

Here the program tries to get the field "MailDomain" in the Table Group =
(g).
This table didn=B4t contain any field named "MailDomain". But the table =
Domain
contains one.

And so I corrected this error by replacing "g" by "d".

The error with the field "MailDomain" occur also the following =
statements:
CREATE PROCEDURE ImailSetUser
CREATE PROCEDURE ListDomainUsers

I got a mail back from IEA-Software, which says that the changes we
made was ok.

I hope I could help you
Logar Harald
Net4You

> -----Urspr=FCngliche Nachricht-----
> Von: Fernando M. Kiernan [SMTP:fkiernan@dilhard.com.ar]
> Gesendet am: Friday, 30 July, 1999 3:20 PM
> An: Emerald@Iea-Software.Com
> Betreff: [Emerald] Emerald 2.5.288 Admin problem.
>=20
>=20
> We downloaded the FULL version of Emerald some days ago and
> have now Emerald 2.5.285 and
> Emerald Admin 2.5.288 running on a NT4SP5 with SQL Server 6.5 SP5.
> After installing this version in Emerald admin the tab that
> shows Server Ports Access
> appears blank.
> Does anybody seen that too?? It was reported a couple of
> days ago to
> support@iea-software.com and stil no response.
>=20
> Thanks in advance.
>
> Fernando M. Kiernan
> Imagenes Digitales S.A.
> DILHARD internet
> fkiernan@dilhard.com.ar
> http://www.dilhard.com.ar
>=20
>=20
>=20
> For more information about this list (including removal) go to:
> http://www.iea-software.com/support/maillists/liststart

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart