I have found problems sometimes when I project a temp table from a view. I have found that immediately reprojecting another temp table from the projected table and using that one instead seems to calm things down.
Dennis McGrath -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of A.G. IJntema Sent: Wednesday, January 27, 2010 4:34 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: seem to be getting I/O errors when updating temp tables on occasion with live data on one users workstation... Hi Rachael, I have had similar kind of problems with Temp tables. One of the causes could be the directory in which these temp tables are stored. Please make sure you have access rights to this directory. You can enforce the directory in which these temp files will be written by the command SET SCRATCH .... Hope it helps Kind Regards, Tony IJntema The Netherlands -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Rachael Malberg Sent: woensdag 27 januari 2010 16:49 To: RBASE-L Mailing List Subject: [RBASE-L] - seem to be getting I/O errors when updating temp tables on occasion with live data on one users workstation... Config.... compiled v8 version 21.30903 (had updated to 11/2 back in Nov but it resulted in massive I/O, Access Violations, and Lock ups so reverted back in the meantime. Plan to update to current vrs in a few weeks.) window network with new server 1 gig switches 1 gig network cards home run cat6 workstation... windows NT 1 gig network card Dual processors 2 gig Ram user... 120/min typer no extra config, dll This seems to happen primarly just before printing one of her reports or in one form selecting records to record in live data. These errors will continue to cycle threw till she ends the program. Often after going back in the errors will not be there and the process works. the errors are... I/O check for full disk then I/O database files out of sync if someone has some idea for resolution here is the most consistant code that errors pop up on... RECALC VAR SET VAR vSalesID INTEGER =(.vSalePayerSalesId) CALL Pause3 () --RptCrtData4Rpt_TransBillingReport --SET ERROR MESSAGES OFF SET VAR RptTBD_DoARCalc TEXT='Y' SET VAR vCheckForTbl INTEGER='0' SELECT COUNT(*) INTO vCheckForTbl FROM SYS_TABLES WHERE SYS_TABLE_NAME ='att_SaleAndCustInfo4TB' IF vCheckForTbl ='0' THEN CREATE TEMPORARY TABLE `att_SaleAndCustInfo4TB` + (`CustCID` INTEGER , + `MedProCAID` INTEGER , + `MedProCid` INTEGER , + `SalesID` INTEGER , + `SaleNotes` NOTE (1000) , + `SalesPersonTxt` TEXT (40) , + `CustName` TEXT (75) , + `CustBirthdate` DATE , + `CustGender` TEXT (1) , + `CustHeight` TEXT (7) , + `CustWeight` TEXT (3) , + `CustNotes` NOTE (4000) , + `CustAlert` TEXT (50) , + `CustRefCAID` INTEGER , + `CustAdd1` TEXT (50) , + `CustAdd2` TEXT (50) , + `CustCSZ` TEXT (75) , + `CustFax` TEXT (20) , + `CustPhone` TEXT (20) , + `CustAddContactName` TEXT (30) , + `CustAddDirections` NOTE (500) , + `CustAddLookUpDirections` NOTE , + `BusLocAddLn` TEXT (50) , + `BusLocCSZ` TEXT (75) , + `BusLocFaxFmt` TEXT (25) , + `BusLocLogo` LONG VARBIT , + `BusLocName` TEXT (30) , + `BusLocPhoneFmt` TEXT (25) , + `BusLocNPI` TEXT (10) , + `DocRefCAID` INTEGER , + `DocName` TEXT (75) , + `DocAddContactName` TEXT (30) , + `DocAdd1` TEXT (50) , + `DocAdd2` TEXT (50) , + `DocCSZ` TEXT (75) , + `DocPhone` TEXT (25) , + `DocFax` TEXT (75) , + `DocContactNotes` TEXT (500), + `DocNPI` TEXT (10) , + `DocLic` TEXT (25) ) ELSE DELETE FROM att_SaleAndCustInfo4TB ENDIF --removed to try and elim i/os APPEND atv_SaleAndCustInfo TO att_SaleAndCustInfo4TB WHERE SalesID=.vSalesID INSERT INTO att_SaleAndCustInfo4TB + CustCID, + MedProCAID, + MedProCid, + SalesID, + SaleNotes, + SalesPersonTxt, + CustName, + CustBirthdate, + CustGender, + CustHeight, + CustWeight, + CustNotes, + CustAlert, + CustRefCAID, + CustAdd1, + CustAdd2, + CustCSZ, + CustFax, + CustPhone, + CustAddContactName, + CustAddDirections, + CustAddLookUpDirections, + BusLocAddLn, + BusLocCSZ, + BusLocFaxFmt, + BusLocLogo, + BusLocName, + BusLocPhoneFmt, + BusLocNPI + SELECT + T1.CID , + T1.MedProCAID, + T1.MedProCid, + T1.SalesID, + ('Encounter Note: '+ T1.SaleNotes), + T6.AppDisplayName, + (IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T3 .CMBusName&'attn:'&T3.CMFname&T3.CMLname))), + T4.CustBirthdate, + T4.CustGender, + T4.CustHeight, + T4.CustWeight, + ('Customer Note: '+ T4.CustNotes), + (IFNULL(T4.CustMajorAlert,T4.CustMajorAlert,'ALERT!!!')), + T5.RefCAID, + T5.CA_Address1, + T5.CA_Address2, + (T5.CA_City+','&T5.CA_State&T5.CA_Zip), + (IFNULL(T5.CA_FaxNum ,T5.CA_FaxNum ,'Fax: ('+SGET(T5.CA_FaxNum,3,1)+')'+SGET(T5.CA_FaxNum ,3,4)+'-'+SGET(T5.CA_FaxNum,4,7))), + (IFNULL(T5.CA_PhoneNum ,T5.CA_PhoneNum ,'Phone: ('+SGET(T5.CA_PhoneNum,3,1)+')'+SGET(T5.CA_PhoneNum ,3,4)+'-'+SGET(T5.CA_PhoneNum,4,7))), + T5.CA_PrimContName, + ('Address Directions / Note: '+ T5.CA_Directions ), + ' ', + T7.BusLocAddLn1, + (T7.BusLocCity+', '+T7.BusLocState&T7.BusLocZip), + (IFNULL(T7.BusLocFax ,T7.BusLocFax ,'Fax: ('+SGET(T7.BusLocFax ,3,1)+')'+SGET(T7.BusLocFax ,3,4)+'-'+SGET(T7.BusLocFax ,4,7))), + T7.BusLocLogo, + T7.BusLocName, + (IFNULL(T7.BusLocPhone,T7.BusLocPhone,'Phone: ('+SGET(T7.BusLocPhone,3,1)+')'+SGET(T7.BusLocPhone,3,4)+'-'+SGET(T7.BusLocP hone,4,7))), + T7.BusLocElectSubNum + FROM + Sale T1, + ContactMaster T3, + Customer T4, + ContactAddress T5, + ZAppAccess T6, + BusinessLocations T7 + WHERE T1.SalesID=.vSalesID AND T1.CID=T3.CID AND T1.CID=T4.CID AND T1.CustResidentialID=T5.CAID AND T1.SalesPerson =T6.AccessID AND T1.SaleBusLocID=T7.BusLocID --update customer address info if address is a look up UPDATE att_SaleAndCustInfo4TB SET + CustAddContactName =T2.CA_PrimContName, + CustAdd1=T2.CA_Address1, + CustAdd2=T2.CA_Address2, + CustCSZ=(T2.CA_City+','&T2.CA_State&T2.CA_Zip), + CustAddLookUpDirections=T2.CA_Directions,CustPhone=(IFNULL(T2.CA_PhoneNum,T2 .CA_PhoneNum,'Phone: ('+SGET(T2.CA_PhoneNum,3,1)+')'+SGET(T2.CA_PhoneNum,3,4)+'-'+SGET(T2.CA_Phon eNum,4,7))), + CustFax =(IFNULL(T2.CA_FaxNum ,T2.CA_FaxNum ,'Fax: ('+SGET(T2.CA_FaxNum ,3,1)+')'+SGET(T2.CA_FaxNum ,3,4)+'-'+SGET(T2.CA_FaxNum ,4,7))) + FROM att_SaleAndCustInfo4TB T1, ContactAddress T2 + WHERE T1.CustRefCAID=T2.CAID UPDATE att_SaleAndCustInfo4TB SET CustAddContactName=' ' WHERE CustRefCAID='0' UPDATE att_SaleAndCustInfo4TB SET + CustAddContactName=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLn ame,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname))) + FROM att_SaleAndCustInfo4TB T1, ContactAddress T2, ContactMaster T3 + WHERE T1.CustRefCAID=T2.CAID AND T2.CID=T3.CID AND CustAddContactName IS NULL --update doc address info if address is a look up UPDATE att_SaleAndCustInfo4TB SET + DocRefCAID=T2.RefCAID , + DocAddContactName=T2.CA_PrimContName, + DocAdd1=T2.CA_Address1, + DocAdd2=T2.CA_Address2, + DocCSZ=(T2.CA_City+','&T2.CA_State&T2.CA_Zip), + DocPhone=(IFNULL(T2.CA_PhoneNum,T2.CA_PhoneNum,'Phone: ('+SGET(T2.CA_PhoneNum,3,1)+')'+SGET(T2.CA_PhoneNum,3,4)+'-'+SGET(T2.CA_Phon eNum,4,7))), + DocFax =(IFNULL(T2.CA_FaxNum,T2.CA_FaxNum,'Fax: ('+SGET(T2.CA_FaxNum,3,1)+')'+SGET(T2.CA_FaxNum,3,4)+'-'+SGET(T2.CA_FaxNum,4 ,7))), + DocContactNotes=T2.CA_Directions + FROM att_SaleAndCustInfo4TB T1, ContactAddress T2 + WHERE T1.MedProCAID=T2.CAID UPDATE att_SaleAndCustInfo4TB SET + DocName =(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLname,T3.CMBusName,T 3.CMBusName&'attn:'&T3.CMFname&T3.CMLname))) + FROM att_SaleAndCustInfo4TB T1, ContactMaster T3 + WHERE T1.MedProCid =T3.CID UPDATE att_SaleAndCustInfo4TB SET + DocAddContactName=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLna me,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname))), + FROM att_SaleAndCustInfo4TB T1, ContactMaster T3 + WHERE T1.MedProCid =T3.CID AND DocAddContactName IS NULL UPDATE att_SaleAndCustInfo4TB SET + DocNPI=T3.MedProNPIID,DocLic = T3.MedProDocLicNo + FROM att_SaleAndCustInfo4TB T1, MedProfessionals T3 + WHERE T1.MedProCid =T3.CID UPDATE att_SaleAndCustInfo4TB SET + DocAddContactName=T2.CA_PrimContName, + DocAdd1=T2.CA_Address1, + DocAdd2=T2.CA_Address2, + DocCSZ=(T2.CA_City+','&T2.CA_State&T2.CA_Zip), + DocPhone=(IFNULL(T2.CA_PhoneNum,T2.CA_PhoneNum,'Phone: ('+SGET(T2.CA_PhoneNum,3,1)+')'+SGET(T2.CA_PhoneNum,3,4)+'-'+SGET(T2.CA_Phon eNum,4,7))), + DocFax =(IFNULL(T2.CA_FaxNum,T2.CA_FaxNum,'Fax: ('+SGET(T2.CA_FaxNum,3,1)+')'+SGET(T2.CA_FaxNum,3,4)+'-'+SGET(T2.CA_FaxNum,4 ,7))) + FROM att_SaleAndCustInfo4TB T1, ContactAddress T2 + WHERE T1.DocRefCAID=T2.CAID UPDATE att_SaleAndCustInfo4TB SET DocAddContactName=' ' WHERE DocRefCAID='0' UPDATE att_SaleAndCustInfo4TB SET + DocAddContactName=(IFNULL(T3.CMBusName,T3.CMFname&T3.CMLname,IFNULL(T3.CMLna me,T3.CMBusName,T3.CMBusName&'attn:'&T3.CMFname&T3.CMLname))) + FROM att_SaleAndCustInfo4TB T1, ContactAddress T2, ContactMaster T3 + WHERE T1.DocRefCAID=T2.CAID AND T2.CID=T3.CID AND DocAddContactName IS NULL -- temp table not always deleting data prior to insert DELETE DUPLICATES FROM att_SaleAndCustInfo4TB USING SalesID SET VAR vCheckForTbl INTEGER='0' SELECT COUNT(*) INTO vCheckForTbl FROM SYS_TABLES WHERE SYS_TABLE_NAME ='att_TransPayer' IF vCheckForTbl ='0' THEN CREATE TEMPORARY TABLE `att_TransPayer` (`SalesID` INTEGER , + `PayerBillID` TEXT (10), + `PayorItem_DOS` DATE , + `SalePayorID` INTEGER , + `SaleItemPayorID` INTEGER , + `CustInsVerID` INTEGER , + `PolicyHolderRelCode` TEXT (2) , + `SubscriberName` TEXT (30) , + `SaleItemID` INTEGER , + `PayerRefCAID` INTEGER , + `CustInsuranceID` INTEGER , + `PayerCAID` INTEGER , + `PayerCID` INTEGER , + `PayerOrder` INTEGER , + `BDPayerStat` TEXT (2) , + `BDBillLine` TEXT (1) , + `BD2BillCnt` INTEGER , + `BDBilledCnt` INTEGER , + `PayerName` TEXT (70) , + `PayerPAContactName` TEXT (30) , + `PayerPAAdr1` TEXT (50) , + `PayerPAAdr2` TEXT (50) , + `PayerPACSZ` TEXT (70) , + `PayerPAPhone` TEXT (25) , + `PayerPAFax` TEXT (25) , + `PayerNotes` NOTE (500) , + `PayorItemName` TEXT (60) , + `PayorLineItemRef` INTEGER , + `BillingBilledCnt` INTEGER , + `BilledQty` INTEGER , + `BillingBilledAmt` CURRENCY , + `BilledItemAmt` CURRENCY , + `BillingHCPCS` TEXT (12) , + `PANum` TEXT (12) , + `BillingModifiers` TEXT (20) , + `BillingDXCodes` TEXT (200) , + `BillingNarative` TEXT (80) , + `BDAdjCodes` TEXT (20) , + `PayerAllowedAmt` CURRENCY , + `PayerPaidAmt` CURRENCY DEFAULT 0, + `BDHasPrevPayer` TEXT (1) DEFAULT 'N', + `ClaimPaidInfo` CURRENCY , + `AmtDue` CURRENCY , + `AmtPaid` CURRENCY DEFAULT 0, + `PolicyNo` TEXT (29) , + `GroupNo` TEXT (29) , + `CustInsNote` NOTE (4000), + `BDACNum` TEXT (50) ) -- removed per christina 12/13 `ClaimPaidInfo`= (IFEQ(BDHasPrevPayer,'Y',(BilledItemAmt-PayerAllowedAmt+PayerPaidAmt),0)) CURRENCY , + ELSE DELETE FROM att_TransPayer ENDIF INSERT INTO att_TransPayer + (CustInsVerID,SalesID, PayerCAID, SalePayorID, PayerCID, CustInsuranceID, PayerOrder, BDPayerStat, PayerNotes, BillingDXCodes, + PayorItem_DOS, SaleItemPayorID, BDBilledCnt , BillingHCPCS, BillingModifiers, BillingNarative, AmtDue , BilledQty, PayerAllowedAmt, SaleItemID, BDACNum, + BDAdjCodes, BilledItemAmt, PANum, + BDBillLine, + PayerPaidAmt) + SELECT + CustInsVerID,SalesID, CAID, SalePayorID, InsCID, CustInsuranceID, PayerOrder, SalePayorStatus, ('Encounter Payer Note: '+ SalePayorNotes), PayerDxCodes, + PayorItem_DOS, SaleItemPayorID, BilledCount, BillingHCPCS, BillingModifiers, BillingNarative, PLTotalCharged, BillingItemQty, AllowedItemTotal, SaleItemID, ACNum, BillingStatGroup, BillingItemBilledTotal, InsPAAuthNumber, + BillThisLine, + TotalExpectedPaidByPrevPayers + FROM SalePayor T1,SalePayorItem2 T2 + WHERE T1.SalePayorID=T2.SalePayorID AND SalesID=.vSalesID AND PayerCat='CI' + ORDER BY PayerOrder,PayorItem_DOS,BillThisLineHow,InsPAAuthNumber,SaleItemPayorID INSERT INTO att_TransPayer + (SaleItemID,PayerOrder,BillingBilledAmt,SaleItemPayorID,BDPayerStat,BDBillLi ne,BDBilledCnt, AmtDue, PayerPaidAmt, PayorItem_DOS, BillingBilledCnt, SalesID, PayerName,SalePayorID, PayorItemName, BilledQty, BilledItemAmt, BillingHCPCS, BillingDXCodes, PayerAllowedAmt, PayerNotes ) + SELECT SaleItemID,T1.PayerOrder,T2.BillingItemBilledTotal,T2.SaleItemPayorID,T1.Sal ePayorStatus,T2.BillThisLine,T2.BilledCount,T2.PLTotalCharged , T2.TotalExpectedPaidByPrevPayers , T2.PayorItem_DOS , T2.BilledCount, T1.SalesID, 'Customer/Customer Contact', T1.SalePayorID, T2.PayorItemName, T2.BillingItemQty, T2.BillingItemBilledTotal, T2.BillingHCPCS, T2.BillingDXCodes, T2.AllowedItemTotal, ('Payer Notes:'+T1.SalePayorNotes) + FROM SalePayor T1,SalePayorItem2 T2 + WHERE T1.SalePayorID=T2.SalePayorID AND SalesID=.vSalesID AND PayerCat='CC' + ORDER BY PayerOrder,PayorItem_DOS,BillThisLineHow,InsPAAuthNumber,SaleItemPayorID -- i/o seem start here... UPDATE att_TransPayer SET + PayerBillID=T3.BillingPayerID , PayerName=(IFNULL(T2.CMBusName,(T2.CMFname&T2.CMMname&T2.CMLname),(T2.CMBusN ame&(IFNULL(T2.CMFname,T2.CMFname,('attn:'&T2.CMFname&T2.CMMname&T2.CMLname) ))))), + PayerPAContactName=(IFEQ(T3.CA_PrimContact,'S',(IFNULL(T2.CMBusName,(T2.CMFn ame&T2.CMMname&T2.CMLname),(T2.CMBusName&(IFNULL(T2.CMFname,T2.CMFname,('att n:'&T2.CMFname&T2.CMMname&T2.CMLname)))))),T3.CA_PrimContName)), PayerPAAdr1=T3.CA_Address1, + PayerPAAdr2=T3.CA_Address2, + PayerPACSZ=(T3.CA_City+','&T3.CA_State&T3.CA_Zip), + PayerPAPhone=(IFNULL(T3.CA_PhoneNum,T3.CA_PhoneNum,'Phone: ('+SGET(T3.CA_PhoneNum,3,1)+')'+SGET(T3.CA_PhoneNum,3,4)+'-'+SGET(T3.CA_Phon eNum,4,7))), + PayerPAFax=(IFNULL(T3.CA_FaxNum,T3.CA_FaxNum,'Fax: ('+SGET(T3.CA_FaxNum,3,1)+')'+SGET(T3.CA_FaxNum,3,4)+'-'+SGET(T3.CA_FaxNum,4 ,7))) + FROM att_TransPayer T1,ContactAddress T3, ContactMaster T2 + WHERE T1.PayerCAID=T3.CAID AND T3.CID=T2.CID UPDATE att_TransPayer SET + PolicyNo=T2.PolicyNo,GroupNo=T2.GroupNo,CustInsNote=('Cust Ins Note: '+ T2.CustInsNote) + FROM att_TransPayer T1,CustInsurance T2 + WHERE T1.CustInsuranceID =T2.CustInsuranceID UPDATE att_TransPayer SET + PolicyholderRelCode=T2.PolicyholderRelCode, SubscriberName=(T2.PolicyHolderFName&T2.PolicyHolderLName ) + FROM att_TransPayer T1,CustInsVerify T2 + WHERE T1.CustInsVerID=T2.CustInsVerID UPDATE att_TransPayer SET + SubscriberName=CustName + FROM att_TransPayer T1,att_SaleAndCustInfo4TB T2 + WHERE T1.SalesID=T2.SalesID AND PolicyholderRelCode='1' SET VAR RptTBD_SaleItemPayorID INTEGER SET VAR RptTBD_P CURRENCY='0' SET VAR RptTBD_R CURRENCY='0' SET VAR RptTBD_B CURRENCY='0' DROP CURSOR GetPayerItems DECLARE GetPayerItems CURSOR FOR SELECT SaleItemPayorID FROM att_TransPayer OPEN GetPayerItems FETCH GetPayerItems INTO RptTBD_SaleItemPayorID WHILE SQLCODE<>100 AND SQLCODE>=0 THEN SELECT SUM(PaymentAmt) INTO RptTBD_P FROM PayerPayments WHERE SaleItemPayorID=.RptTBD_SaleItemPayorID AND PaymentAction IN ('w','-') SELECT SUM(PaymentAmt) INTO RptTBD_R FROM PayerPayments WHERE SaleItemPayorID=.RptTBD_SaleItemPayorID AND PaymentAction='+' SET VAR RptTBD_B=(.RptTBD_P-.RptTBD_R) UPDATE att_TransPayer SET + AmtPaid=(.RptTBD_B) + WHERE SaleItemPayorID=.RptTBD_SaleItemPayorID SET VAR RptTBD_P CURRENCY='0' SET VAR RptTBD_R CURRENCY='0' SET VAR RptTBD_B CURRENCY='0' FETCH GetPayerItems INTO RptTBD_SaleItemPayorID ENDWHILE DROP CURSOR GetPayerItems --end of sp PRINT TransBillingReport ARRANGE att_TransPayer BY PayerOrder WHERE SalesID=.vSalesID OPTION SCREEN|ZOOM_TYPE 100_PERCENT|WINDOW_STATE MAXIMIZED RETURN __________ Informatie van ESET NOD32 Antivirus, versie van database viruskenmerken 4810 (20100127) __________ Het bericht is gecontroleerd door ESET NOD32 Antivirus. http://www.eset.com
