quote:

---

CODE
USE [Harvest] GO /****** Object: StoredProcedure [dbo].[uspProcessDocumentDeleteQueue] Script Date: 10/22/2008 10:11:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[uspProcessDocumentDeleteQueue] AS --Set up a counter so we know when all are processed DECLARE @count int SET @count = (SELECT COUNT(*) FROM dbo.DocumentDeleteQueue ddq WHERE ddq.Deleted = 0) --Main loop - runs untill all are processed WHILE @count > 0 BEGIN -- mail loop --Set the DocumentDeleteID as next one in line DECLARE @dID uniqueidentifier SET @dID = (SELECT TOP 1 ddq.DocumentDeleteID FROM dbo.DocumentDeleteQueue ddq WHERE ddq.Deleted = 0 ORDER BY ddq.DeleteDate) --Is it a Receipt or DA/TI DECLARE @doc nvarchar(256) SET @doc = (SELECT TOP 1 dt.DocumentType FROM dbo.DocumentDeleteQueue ddq INNER JOIN dbo.DocumentType dt ON dt.DocumentTypeID = ddq.DocumentTypeID WHERE ddq.DocumentDeleteID = @dID) --Get the Document ID, could be either DECLARE @docID bigint -- Get a genericID, could be either SET @docID = ( SELECT TOP 1 DocumentID FROM dbo.DocumentDeleteQueue ddq WHERE ddq.DocumentDeleteID = @dID) --If it is a receipt IF @doc = 'Receipt' BEGIN -- Start receipt --Perform the Receipt specific process0 --For Test SELECT 'Do Receipt' --Find the EntryID for the Receipt and put it in a variable --DECLARE @eID uniqueidentifier --SET @eID = (SELECT TOP 1 EntryID FROM dbo.Receipt -- top 1 because must select only 1 -- WHERE ReceiptID = @docID) -- ----Put the EntryTask Info for the Entry into the DocumentTask Table --INSERT INTO dbo.DocumentDeleteTask --( DocumentDeleteID, -- TaskID , -- UserID ) --SELECT @dID , -- et.TaskID , -- et.UserID --FROM dbo.EntryTask et --WHERE et.EntryID = @eID -- ----Add Current User as the Deleter (not sure how to, may be a parm coming in) ----Need to do this before actual deletes start ----NOTE could be a proc itself as it will be common to both types -- -- ----Call Entry Purge to do the actual delete -- --EXEC Harvest.dbo.uspAccountPurge @eID END -- End receipt ELSE --BEGIN --SELECT 'Do any other' --Find the EntryID for the DA/TI, Join to Journal, as DA table does not have it DECLARE @eID uniqueidentifier SET @eID = (SELECT TOP 1 daj.EntryID FROM dbo.DebitAuthorityJournal daj -- top 1 because must select only 1 INNER JOIN dbo.DebitAuthority da ON da.DebitAuthorityID = daj.DebitAuthorityID WHERE da.DebitAuthorityID = @docID) --If Null suggests to look at Company IF @eID IS NULL BEGIN --Same as above but looks at Company versions SET @eID = (SELECT TOP 1 dacj.EntryID FROM dbo.DebitAuthorityCompanyJournal dacj -- top 1 because must select only 1 INNER JOIN dbo.DebitAuthority da ON da.DebitAuthorityID = dacj.DebitAuthorityID WHERE da.DebitAuthorityID = @docID) --If all is well should have the entry ID in eID --So now what? INSERT INTO dbo.DocumentDeleteTask ( DocumentDeleteID, TaskID , UserID ) SELECT @dID , et.TaskID , et.UserID FROM dbo.EntryTask et WHERE et.EntryID = @eID ----Question - What if the Number was wrong? - 9999999999 ---- leave for later but whateverwe do here should also happen for receipts EXEC Harvest.dbo.uspAccountPurge @eID UPDATE dbo.DocumentDeleteQueue SET Deleted = 1 , DeleteDate = GETDATE() WHERE DocumentDeleteID = @dID ----Reset the count to make sure the loop finishes when done SET @count = (SELECT COUNT(*) FROM dbo.DocumentDeleteQueue ddq WHERE ddq.Deleted = 0) END -- Main loop for updating the document delete table END-- --UPDATE dbo.DocumentDeleteQueue --SET Deleted = 0

---

quote:

---

--Delete Debit Authority First
DELETE dbo.DebitAuthorityJournal
WHERE DebitAuthorityID = @debitAuthorityID

DELETE dbo.DebitAuthorityCompanyJournal
WHERE DebitAuthorityID = @debitAuthorityID

DELETE dbo.DebitAuthority
WHERE DebitAuthorityID = @debitAuthorityID

---

INSERT INTO @jnl ( EntryID ) 
SELECT EntryID FROM dbo.DebitAuthorityJournal 
WHERE DebitAuthorityID = @debitAuthorityID 
UNION ALL
SELECT EntryID FROM dbo.DebitAuthorityCompanyJournal 
WHERE DebitAuthorityID = @debitAuthorityID 
--SELECT * FROM @jnl 

quote:

---

Originally posted by rjhe25

when i run it nothing happens my app freezes and when i run just the query it keeps running and never stops. the test is only deleteing one thing so it should happen quickly

---

quote:

---

Originally posted by rjhe25

well i think the problem is getting value of @debitAuthorityID dont no if thats right it was just a thing i tried

---

quote:

---

DECLARE @count int
SET @count = (SELECT COUNT(*)
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.Deleted = 0)


--Main loop - runs untill all are processed

WHILE @count > 0
BEGIN -- mail loop


--Set the DocumentDeleteID as next one in line
DECLARE @dID uniqueidentifier
SET @dID = (SELECT TOP 1 ddq.DocumentDeleteID
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.Deleted = 0
ORDER BY ddq.DeleteDate)

--Is it a Receipt or DA/TI
DECLARE @doc nvarchar(256)
SET @doc = (SELECT TOP 1 dt.DocumentType
FROM dbo.DocumentDeleteQueue ddq
INNER
JOIN dbo.DocumentType dt
ON dt.DocumentTypeID = ddq.DocumentTypeID
WHERE ddq.DocumentDeleteID = @dID)

--Get the Document ID, could be either
DECLARE @docID bigint -- Get a genericID, could be either
SET @docID = ( SELECT TOP 1 DocumentID
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.DocumentDeleteID = @dID)


--If it is a receipt
IF @doc = 'Receipt'
BEGIN -- Start receipt

--Perform the Receipt specific process0
--For Test
SELECT 'Do Receipt'


--Find the EntryID for the Receipt and put it in a variable
--DECLARE @eID uniqueidentifier
--SET @eID = (SELECT TOP 1 EntryID FROM dbo.Receipt -- top 1 because must select only 1
-- WHERE ReceiptID = @docID)
--
----Put the EntryTask Info for the Entry into the DocumentTask Table
--INSERT INTO dbo.DocumentDeleteTask
--( DocumentDeleteID,
-- TaskID ,
-- UserID )
--SELECT @dID ,
-- et.TaskID ,
-- et.UserID
--FROM dbo.EntryTask et
--WHERE et.EntryID = @eID
--
----Add Current User as the Deleter (not sure how to, may be a parm coming in)
----Need to do this before actual deletes start
----NOTE could be a proc itself as it will be common to both types
--
--
----Call Entry Purge to do the actual delete
--
--EXEC Harvest.dbo.uspAccountPurge @eID


END -- End receipt
ELSE
BEGIN
--SELECT 'Do any other'

--Find the EntryID for the DA/TI, Join to Journal, as DA table does not have it
DECLARE @eID uniqueidentifier
SET @eID = (SELECT TOP 1 daj.EntryID
FROM dbo.DebitAuthorityJournal daj -- top 1 because must select only 1
INNER
JOIN dbo.DebitAuthority da
ON da.DebitAuthorityID =daj.DebitAuthorityID
WHERE da.DebitAuthorityID = @docID)

--If Null suggests to look at Company
IF @eID IS NULL
BEGIN
--Same as above but looks at Company versions
SET @eID = (SELECT TOP 1 dacj.EntryID
FROM dbo.DebitAuthorityCompanyJournal dacj -- top 1 because must select only 1
INNER
JOIN dbo.DebitAuthority da
ON da.DebitAuthorityID = dacj.DebitAuthorityID
WHERE da.DebitAuthorityID = @docID)



--If all is well should have the entry ID in eID
--So now what?
--INSERT INTO dbo.DocumentDeleteTask
--( DocumentDeleteID,
-- TaskID ,
-- UserID )
--SELECT @dID ,
-- et.TaskID ,
-- et.UserID
--FROM dbo.EntryTask et
--WHERE et.EntryID = @eID

----Question - What if the Number was wrong? - 9999999999
---- leave for later but whateverwe do here should also happen for receipts

EXEC Harvest.dbo.uspAccountPurge @eID


UPDATE dbo.DocumentDeleteQueue
SET Deleted = 1 ,
DeleteDate = GETDATE()
WHERE DocumentDeleteID = @dID

----Reset the count to make sure the loop finishes when done
SET @count = (SELECT COUNT(*)
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.Deleted = 0)


END -- Main loop for updating the document delete table
END
end
--
--UPDATE dbo.DocumentDeleteQueue
--SET Deleted = 0

---

quote:

---

Originally posted by rjhe25

i dont think the @debitauthority is right. it does not get a value.
i just have it like this at the top
( @debitAuthorityID bigint )

---

quote:

---

GO
/****** Object: StoredProcedure [dbo].[uspProcessDocumentDeleteQueue] Script Date: 10/22/2008 10:11:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[uspProcessDocumentDeleteQueue]
AS

--Set up a counter so we know when all are processed
DECLARE @count int
SET @count = (SELECT COUNT(*)
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.Deleted = 0)


--Main loop - runs untill all are processed

WHILE @count > 0
BEGIN -- mail loop


--Set the DocumentDeleteID as next one in line
DECLARE @dID uniqueidentifier
SET @dID = (SELECT TOP 1 ddq.DocumentDeleteID
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.Deleted = 0
ORDER BY ddq.DeleteDate)

--Is it a Receipt or DA/TI
DECLARE @doc nvarchar(256)
SET @doc = (SELECT TOP 1 dt.DocumentType
FROM dbo.DocumentDeleteQueue ddq
INNER
JOIN dbo.DocumentType dt
ON dt.DocumentTypeID = ddq.DocumentTypeID
WHERE ddq.DocumentDeleteID = @dID)

--Get the Document ID, could be either
DECLARE @docID bigint -- Get a genericID, could be either
SET @docID = ( SELECT TOP 1 DocumentID
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.DocumentDeleteID = @dID)


--If it is a receipt
IF @doc = 'Receipt'
BEGIN -- Start receipt

--Perform the Receipt specific process0
--For Test
SELECT 'Do Receipt'


--Find the EntryID for the Receipt and put it in a variable
--DECLARE @eID uniqueidentifier
--SET @eID = (SELECT TOP 1 EntryID FROM dbo.Receipt -- top 1 because must select only 1
-- WHERE ReceiptID = @docID)
--
----Put the EntryTask Info for the Entry into the DocumentTask Table
--INSERT INTO dbo.DocumentDeleteTask
--( DocumentDeleteID,
-- TaskID ,
-- UserID )
--SELECT @dID ,
-- et.TaskID ,
-- et.UserID
--FROM dbo.EntryTask et
--WHERE et.EntryID = @eID
--
----Add Current User as the Deleter (not sure how to, may be a parm coming in)
----Need to do this before actual deletes start
----NOTE could be a proc itself as it will be common to both types
--
--
----Call Entry Purge to do the actual delete
--
--EXEC Harvest.dbo.uspAccountPurge @eID


END -- End receipt
ELSE
BEGIN
--SELECT 'Do any other'

--Find the EntryID for the DA/TI, Join to Journal, as DA table does not have it
DECLARE @eID uniqueidentifier
SET @eID = (SELECT TOP 1 daj.EntryID
FROM dbo.DebitAuthorityJournal daj -- top 1 because must select only 1
INNER
JOIN dbo.DebitAuthority da
ON da.DebitAuthorityID = daj.DebitAuthorityID
WHERE da.DebitAuthorityID = @docID)

--If Null suggests to look at Company
IF @eID IS NULL
BEGIN
--Same as above but looks at Company versions
SET @eID = (SELECT TOP 1 dacj.EntryID
FROM dbo.DebitAuthorityCompanyJournal dacj -- top 1 because must select only 1
INNER
JOIN dbo.DebitAuthority da
ON da.DebitAuthorityID = dacj.DebitAuthorityID
WHERE da.DebitAuthorityID = @docID)



--If all is well should have the entry ID in eID
--So now what?
--INSERT INTO dbo.DocumentDeleteTask
--( DocumentDeleteID,
-- TaskID ,
-- UserID )
--SELECT @dID ,
-- et.TaskID ,
-- et.UserID
--FROM dbo.EntryTask et
--WHERE et.EntryID = @eID

----Question - What if the Number was wrong? - 9999999999
---- leave for later but whateverwe do here should also happen for receipts

EXEC Harvest.dbo.uspAccountPurge @eID


UPDATE dbo.DocumentDeleteQueue
SET Deleted = 1 ,
DeleteDate = GETDATE()
WHERE DocumentDeleteID = @dID

----Reset the count to make sure the loop finishes when done
SET @count = (SELECT COUNT(*)
FROM dbo.DocumentDeleteQueue ddq
WHERE ddq.Deleted = 0)


END -- Main loop for updating the document delete table
END
end
--
--UPDATE dbo.DocumentDeleteQueue
--SET Deleted = 0

---

quote:

---

/****** Object: StoredProcedure [dbo].[uspAccountPurge] Script Date: 10/21/2008 16:36:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspAccountPurge]
( @entryID uniqueidentifier)
AS
DECLARE @jnl TABLE
( EntryID uniqueidentifier )
INSERT INTO @jnl (DebitAuthorityID )
SELECT DebitAuthorityID FROM dbo.DebitAuthorityJournal
WHERE EntryID = @entryID

INSERT INTO @jnl(DebitAuthorityID )
SELECT EntryID FROM dbo.DebitAuthorityCompanyJournal
WHERE EntryID = @entryID
--SELECT * FROM @jnl




--Delete Debit Authority First
DELETE dbo.DebitAuthorityJournal
WHERE EntryID = @entryID

DELETE dbo.DebitAuthorityCompanyJournal
WHERE EntryID = @entryID

DELETE dbo.DebitAuthority
WHERE EntryID = @entryID

--Trade level
DECLARE @trade TABLE ( TradeID uniqueidentifier)
INSERT INTO @trade ( TradeID )
SELECT ij.TradeID
FROM dbo.InvestmentJournal ij
WHERE ij.EntryID = @entryID

--Delete Investment
--Journal
DELETE ij
FROM dbo.InvestmentJournal ij
INNER
JOIN @trade t
ON t.TradeID = ij.TradeID

-------------------------------------------------------------------------------------------
--Legacy Process
--Find the Legacy OrderID
DECLARE @oID bigint
SET @oID = (SELECT TOP 1 OrderID
FROM HarvestBridge.dbo.TradeMap tm
INNER
JOIN @trade t
ON t.TradeID = tm.TradeID)
IF NOT @oiD IS NULL
BEGIN
EXEC HarvestBridge.dbo.uspLegacyOrderDelete @oID
END
--------------------------------------------------------------------------------------------


--------Deposit Receipt
------DELETE r
------FROM dbo.Receipt r
------WHERE r.EntryID = @entryID
----
----DELETE daj
----FROM dbo.DebitAuthorityJournal daj
----WHERE daj.EntryID = @entryID
----
----
------Deposit Journal
----DELETE dj
----FROM dbo.DepositJournal dj
----WHERE dj.EntryID = @entryID
----
----DELETE dj
----FROM dbo.DepositCompanyJournal dj
----WHERE dj.EntryID = @entryID
----
----
------Broker
----DELETE ib
----FROM dbo.InvestmentBroker ib
----INNER
----JOIN @trade t
----ON t.TradeID = ib.TradeID
----
------Basis
----DELETE ib
----FROM dbo.InvestmentBasis ib
----INNER
----JOIN @trade t
----ON t.TradeID = ib.TradeID
----
------Ledger
----DELETE il
----FROM dbo.InvestmentLedger il
----INNER
----JOIN @trade t
----ON t.TradeID = il.TradeID
----
------EntryTask
----DELETE et
----FROM dbo.EntryTask et
----WHERE et.EntryID = @entryID
----
------Delete Company Entries
----DELETE ccj
----FROM dbo.ClientCompanyJournal ccj
----WHERE ccj.ClientEntryID = @entryID
----
------Delete Company Entries
----DELETE ccj
----FROM dbo.ClientCompanyJournal ccj
----WHERE ccj.CompanyEntryID = @entryID
----
------Delete Ledger
----DELETE cl
----FROM dbo.ClientLedger cl
----WHERE cl.EntryID = @entryID
----
----DELETE cl
----FROM dbo.CompanyLedger cl
----WHERE cl.EntryID = @entryID
----
--
----Delete Investment
----Journal
--DELETE ij
--FROM dbo.InvestmentJournal ij
--INNER
--JOIN @trade t
--ON t.TradeID = ij.TradeID
--
----Broker
--DELETE ib
--FROM dbo.InvestmentBroker ib
--INNER
--JOIN @trade t
--ON t.TradeID = ib.TradeID
--
----Basis
--DELETE ib
--FROM dbo.InvestmentBasis ib
--INNER
--JOIN @trade t
--ON t.TradeID = ib.TradeID
--
----Ledger
--DELETE il
--FROM dbo.InvestmentLedger il
--INNER
--JOIN @trade t
--ON t.TradeID = il.TradeID
--
--
----Delete EntryTask
--DELETE et
--FROM dbo.EntryTask et
--INNER
--JOIN @jnl j
--ON j.EntryID = et.EntryID
--
--
----Delete Company Entries
--DELETE ccj
--FROM dbo.ClientCompanyJournal ccj
--INNER
--JOIN @jnl j
--ON j.EntryID = ccj.ClientEntryID
--
--DELETE ccj
--FROM dbo.ClientCompanyJournal ccj
--INNER
--JOIN @jnl j
--ON j.EntryID = ccj.CompanyEntryID
--
----Delete Ledger
--DELETE cl
--FROM dbo.ClientLedger cl
--INNER
--JOIN @jnl j
--ON j.EntryID = cl.EntryID
--
--DELETE cl
--FROM dbo.CompanyLedger cl
--INNER
--JOIN @jnl j
--ON j.EntryID = cl.EntryID

---