Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:
Store Procedure:
--Create Procedure dbo.IMS_Donation
--AS
Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO
SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO
SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID
GO
SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE
Error Message:
(845 row(s) affected)
(844 row(s) affected)
(396 row(s) affected)
Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.
First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).
If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.
If that doesn't help, buy more disk!
-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.
