Dan Holmes
3/27/2007 9:02:00 PM
Msg 8624, Level 16, State 1, Procedure PurgeReceipts, Line 14
Internal SQL Server error.
This is on a SQL 2k server. I tried the same on a 2k5 server and it
worked fine. I don't know how to troubleshoot this.
The print statements do not show.
CREATE PROCEDURE PurgeReceipts
AS
PRINT 'In Purge'
DECLARE @interfaceID varchar(100)
DECLARE @sectionID varchar(100)
DECLARE @keyID varchar(100)
DECLARE @keyvalue varchar(100)
DECLARE @retentionDays INT
SELECT @interfaceID = 'SRECEIPT', @sectionID = 'Purge', @keyID =
'Retention Days'
--the purge parameters are component based values therefore the pc
information isn't necessary
--EXEC profileGetKeyValue1 '', '', '', @interfaceID, @sectionID, @keyID,
@keyvalue OUTPUT
PRINT @keyValue
SET @retentionDays = 500
DELETE FROM receipts WHERE receiptid IN
(SELECT r.receiptid
FROM receipts r
WHERE NOT EXISTS (SELECT * FROM unit WHERE unitid in
(SELECT controlid
FROM receiptchild
WHERE receiptid = r.receiptid
AND interfaceid= 'SUNIT'
))
AND NOT EXISTS (SELECT * FROM container WHERE containerid in
(SELECT controlid
FROM receiptchild
WHERE receiptid = r.receiptid
AND interfaceid= 'SCONTAINER'
))
AND (SELECT MAX(storedate) FROM receipthistory WHERE
receiptid = r.receiptid) <= DATEDIFF(d ,@retentionDays ,GETDATE())
);
PRINT 'HERE'
DELETE FROM masterreceipts WHERE NOT EXISTS
(SELECT receiptid
FROM receipts
WHERE masterreceiptid = masterreceipts.masterreceiptid
);
GO
EXEC PurgeReceipts