Here is the whole script I can, trying to reclaim some data out of a badly
damaged calls table. I trimmed literally 1.2GB of data from the calls
table. But I have NO Primary Keys
select * into calls2 from calls where acctstatustype = 2 and date >=
'6/30/1999 0:00:01 AM' order by calldate
go
create clustered index cdateidx on calls2(calldate)
go
create index cunameidx on calls2(username)
go
alter table calls2 add constraint pk_calls2 unique (NasIdentifier, NasPort,
AcctSessionID, AcctStatusType, UserName)
go
sp_rename calls, oldcalls
go
sp_rename calls2, calls
go
truncate table oldcalls
go
drop table oldcalls
go
alter table calls add constraint pk_calls unique (NasIdentifier, NasPort,
AcctSessionID, AcctStatusType, UserName)
go
alter table calls drop constraint pk_calls2
go
CREATE INDEX iNASIdentifier ON dbo.Calls(NASIdentifier)
GO
CREATE INDEX iNASPort ON dbo.Calls(NASport)
GO
CREATE TRIGGER calls_insert ON dbo.Calls
FOR INSERT
AS
UPDATE ServerPorts
Set ServerPorts.UserName = i.UserName,
ServerPorts.AcctStatusType = i.AcctStatusType,
ServerPorts.CallDate = i.CallDate,
ServerPorts.FramedAddress = i.FramedAddress
FROM Servers s, ServerPorts, inserted i
WHERE s.IPAddress = i.NASIdentifier AND
s.ServerID = ServerPorts.ServerID AND
ServerPorts.Port = i.NASPort
UPDATE customer
Set customer.timeleft=customer.timeleft - (i.AcctSessionTime/60 + 1)
FROM customer, inserted i
WHERE customer.username=i.username
and customer.timeleft <> NULL
and i.AcctStatusType = 2
GO
----- Whatever you do, work at it with all your heart, as working for the
Lord, not for men >Colossians 3:23 (NIV)
John C. Lange, Sr. PALACE dot NET, INC.
microjl@palacenet.net MICRO-TECH Computers, Inc.
608.742.1601 & 6980 1819 New Pinery Road
http://www.palacenet.net/ Portage, WI 53901
MSCE Training for only $150.00 - http://dpec.palacenet.net/
All 200+ Web Based Courses for $150.00 Per Year - "Really"
--- __o
--- _-\<,_ Fastest Service in Town
--- (_)/ (_)
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart