Stored Procedure: SQL & DBF

[nicholasting]

I use stored Procedure to do import data from DBF database(source files)into SQL server database. the process of the stored procedure is run successfully, but data is not insert into SQL server. sometimes it it is import succesfully into SQL server with the same DBF database(source files).

my DBF's (source files) are contain same data, same structure even thought the import process is successfully or unsuccessfully.

my stored procedure have validation to check when import data from DBF to SQL successfully or not.

------------sample----------------------
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
----------------------------------------

after all the process can go through, end of the procedure i do have COMMIT TRAN.


is that any comments?

regards,
nicholas

 

[SQLDenis]

can you post the complete code?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nicholasting]

/*
<VERSION> 1.0.0.7 </VERSION>
ADDED: Fixed to return set @@ERROR to @ERROR_CD to return actual error

<VERSION> 1.0.0.6 </VERSION>
ADDED: TO ENABLE THIS IMPORTSALES2 TO BE ABLE IMPORT IN THE SALES FOR DIFFERENT POS NO (ONE OUTLET THAT HAVING MANY POS)
THE THING AFFECTED IS THE AVERAGE COST BECAUSE OF THE SA_OUTLET_CD IS GETTING FROM THE FILENAME AND AFFECT THE SPUPDAVGCOST
SO NOW IS GET THE SA_OUT_CD FROM CONTROL.DBF


<VERSION> 1.0.0.5 </VERSION>
MODIFIED BY YOHAN
ADDED: UPDATING THE STAFF_S_CD AT HEADER TABLE IS NOT WORKING AND CURRENTLY THERE IS SOME TRANSACTION IS NOT HAVING THIS STAFF_S_CD SO CHANGE AS BELOW
PREVIOUS:
DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM #TEMP_EMPTRAN
CHANGE TO:
DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM EMPTRAN

<VERSION> 1.0.0.4 </VERSION>
MODIFIED BY YOHAN
ADDED: - IMPORT THE DEPOSIT TABLE
- IMPORT THE PRICECHG TABLE
- IMPORT THE STAFF PURCHASE TABLE

<VERSION> 1.0.0.3 </VERSION>
*/
CREATE PROCEDURE [dbo].[spImportSales2]
@DIRNAME AS VARCHAR(100)=NULL,
@FILENAME AS VARCHAR(200)=NULL,
@RESULT INT=0 OUTPUT
AS
DECLARE @TBLNAME AS VARCHAR(100)
DECLARE @PREFIX AS VARCHAR(2)
DECLARE @SA_OUTLET_CD AS VARCHAR(20)
DECLARE @BN_DT AS VARCHAR(20)
DECLARE @TEMP_TABLE AS VARCHAR(50)
DECLARE @TEMP_HEADER AS VARCHAR(50)
DECLARE @TEMP_DETAIL AS VARCHAR(50)
DECLARE @TEMP_PAYMT AS VARCHAR(50)
DECLARE @TEMP_IBALANCE AS VARCHAR(50)
DECLARE @SQLSTRING VARCHAR(3000)
DECLARE @POSNO VARCHAR(10)
DECLARE @ACC_SA_OUT_CD VARCHAR(10)
DECLARE @TOTAL_PROCESSED INT

DECLARE @ERROR_CD INT
SET @TOTAL_PROCESSED=0
-- SELECT * FROM TRN_POS_LOG WHERE [FILENAME]=@FILENAME AND LOG_TYPE='S' --s for sales
IF NOT EXISTS(SELECT * FROM TRN_POS_LOG WHERE [FILENAME]=@FILENAME AND LOG_TYPE='S')
BEGIN

-- Getting the sa_outlet_cd from the filename is not correct if one outlet has many pos
-- so the solution currently is to get from control.dbf that has been imported in

--GET SALES OUTLET CODE FROM FILENAME
/* SELECT @SA_OUTLET_CD=SUBSTRING(@FILENAME, CHARINDEX('.', @FILENAME)+1, 5) */


SELECT @SA_OUTLET_CD = SA_OUT_CD FROM #TEMP_CONTROL

--GET POSNO
SELECT @POSNO=SUBSTRING(@FILENAME, CHARINDEX('.', @FILENAME)+1, 5)
SELECT @ACC_SA_OUT_CD = SA_OUT_CD FROM MAS_POS WHERE POS_NO = @POSNO
--IF @ACC_SA_OUT_CD <> '' @SA_OUTLET_CD = @ACC_SA_OUT_CD
--GENERATE BUSINESS DATE FROM FILENAME
SELECT @BN_DT=SUBSTRING(@FILENAME,1,2)+' '+
CASE SUBSTRING(@FILENAME,3,2)
WHEN '01' THEN 'JAN'
WHEN '02' THEN 'FEB'
WHEN '03' THEN 'MAR'
WHEN '04' THEN 'APR'
WHEN '05' THEN 'MAY'
WHEN '06' THEN 'JUN'
WHEN '07' THEN 'JUL'
WHEN '08' THEN 'AUG'
WHEN '09' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DEC'
END+' '+SUBSTRING(@FILENAME,5,2)
BEGIN TRAN

--############## UPDATE IDETAIL,IBALANCE,IHEADER DATE FOLLOW WITH BUS_DATE IN CONTROL
DECLARE @BUSS_DT DATETIME
SELECT @BUSS_DT=BUS_DATE FROM #TEMP_CONTROL

UPDATE #TEMP_IBALANCE SET BN_DATE = @BUSS_DT
UPDATE #TEMP_IHEADER SET BN_DT = @BUSS_DT
UPDATE #TEMP_IDETAIL SET BN_DT = @BUSS_DT
--############## UPDATE IDETAIL,IBALANCE,IHEADER DATE FOLLOW WITH BUS_DATE IN CONTROL

--##############
--HEADER
--##############
INSERT INTO HEADER
SELECT * FROM #TEMP_HEADER

UPDATE HEADER SET ROUND_AMT=0 WHERE ROUND_AMT IS NULL
--##############
--HEADER
--##############
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--DETAIL
--##############
INSERT INTO DETAIL
(CO_CD, SA_OUT_CD, POS_NO, SHF_NO, STAFF_C_CD,
STAFF_S_CD, TRN_NO, RCP_NO, ORD_NO, TRN_DT, TRN_TM, VOID_TM, BN_DT, TRN_TYPE,
PROD_CD, PROD_G01, PROD_G02, PROD_SIZE, PROD_COLOR, PROD_NM, SA_QTY, SA_PRICE,
SA_AMOUNT, PROD_COST, DISCOUNT, MULTI_DSC, NO_DISC, BARCODE, INV_QTY, TAX_1, TAX_2,
TAX_3, TAX_4, BONUS_PT, BUY_TYPE, P_LEVEL, PROMO_CD, PROMO_AMT, PROD_COMM, PRT_BILL,
PRT_KPT, KPT_NO, CONDIMENT, SEARCH_GRP, PROD_TYPE, STK_CONTRL)
SELECT CO_CD, SA_OUT_CD, POS_NO, SHF_NO, STAFF_C_CD,
STAFF_S_CD, TRN_NO, RCP_NO, ORD_NO, TRN_DT, TRN_TM, VOID_TM, BN_DT, TRN_TYPE,
PROD_CD, PROD_G01, PROD_G02, PROD_SIZE, PROD_COLOR, PROD_NM, SA_QTY, SA_PRICE,
SA_AMOUNT, PROD_COST, DISCOUNT, MULTI_DSC, NO_DISC, BARCODE, INV_QTY, TAX_1, TAX_2,
TAX_3, TAX_4, BONUS_PT, BUY_TYPE, P_LEVEL, PROMO_CD, PROMO_AMT, PROD_COMM, PRT_BILL,
PRT_KPT, KPT_NO, CONDIMENT, SEARCH_GRP, PROD_TYPE, STK_CONTRL FROM #TEMP_DETAIL SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END

/*
--UPDATE AVECOST OF IDETAIL
EXEC spUpdAveCost @SA_OUTLET_CD , @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--UPDATE PROD TYPE OF IDETAIL
EXEC spUpdDetailProdType @SA_OUTLET_CD , @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
*/

--##############
--DETAIL
--##############
--##############
--PAYMENT
--##############
INSERT INTO PAYMENT
SELECT * FROM #TEMP_PAYMENT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--PAYMENT
--##############
--##############
--CONTROL
--##############
INSERT INTO CONTROL
SELECT * FROM #TEMP_CONTROL
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--CONTROL
--##############
--##############
--HOUR SALE
--##############
INSERT INTO HOURSALE
SELECT * FROM #TEMP_HOURSALE
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--HOUR SALE
--##############
--##############
--PLU SALE
--##############
EXEC spImportPLUSALE @BN_DT, @ACC_SA_OUT_CD ,'N'
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--PLU SALE
--##############
--##############
--TRAN SALE
--##############
INSERT INTO TRANSALE
SELECT * FROM #TEMP_TRANSALE
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
EXEC spImportOutletTransale @SA_OUTLET_CD, @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END

--EXEC spIMPORTNEWTRANSALE @SA_OUTLET_CD, @BN_DT
--##############
--TRAN SALE
--##############
--##############
--DEPOSIT
--##############
/* INSERT INTO DEPOSIT
SELECT * FROM #TEMP_DEPOSIT
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
*/
--##############
--DEPOSIT
--##############

--##############
--PRICECHG
--##############
/*
INSERT INTO TRN_POS_CHG
SELECT * FROM #TEMP_PRICECHG
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
*/
--##############
---PRICECHG
--##############

--##############
--EMPTRAN
--##############
/*
INSERT INTO EMPTRAN
SELECT * FROM #TEMP_EMPTRAN
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END

-- TO RECORD THE STAFF PURCHASE
DECLARE @CO_CD VARCHAR(5)
DECLARE @SA_OUT_CD VARCHAR(5)
DECLARE @POS_NO VARCHAR(5)
DECLARE @EMP_CD VARCHAR(15)
DECLARE @BN_DT2 DATETIME
DECLARE @TRN_DT DATETIME
DECLARE @RCP_NO VARCHAR(30)

--- CHANGES AS VERSION 1.0.0.5
---- DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM #TEMP_EMPTRAN
DECLARE CUR_STAFF_PURCH CURSOR FOR SELECT CO_CD, SA_OUT_CD, POS_NO, EMP_CD, BN_DT, TRN_DT, RCP_NO FROM EMPTRAN

OPEN CUR_STAFF_PURCH

FETCH CUR_STAFF_PURCH INTO @CO_CD, @SA_OUT_CD, @POS_NO, @EMP_CD, @BN_DT2, @TRN_DT, @RCP_NO

WHILE @@FETCH_STATUS=0
BEGIN
UPDATE HEADER SET STAFF_S_CD = @EMP_CD WHERE CO_CD=@CO_CD AND SA_OUT_CD=@SA_OUT_CD AND POS_NO=@POS_NO AND BN_DT=@BN_DT2 AND TRN_DT=@TRN_DT AND RCP_NO=@RCP_NO
FETCH CUR_STAFF_PURCH INTO @CO_CD, @SA_OUT_CD, @POS_NO, @EMP_CD, @BN_DT2, @TRN_DT, @RCP_NO
END
*/

--##############
---EMPTRAN
--##############

--##############
--IBALANCE
--##############
--ADDED BY MAGENDREN FOR SALES TO SALES ORDER POSTING FOR DMK
--BEGIN
INSERT INTO IBALANCE
(BN_DATE, STK_LOC, PROD_CD, PROD_G01, PROD_G02, PROD_NM, STOCK_UOM, LAST_COST, PRE_COST, AVE_COST,
OPEN_STK, STK_RECV, STK_RETURN, TRANFERIN, TRANFEROUT, STK_USAGE, STK_ADJ, STK_WASTE, STK_STAFF, STK_GUEST,
BOM, STK_CONV, STK_VAR, TDY_USAGE, STK_CLOSE )
SELECT BN_DATE, STK_LOC, PROD_CD, PROD_G01, PROD_G02, PROD_NM, STOCK_UOM, LAST_COST, PRE_COST, AVE_COST,
OPEN_STK, STK_RECV, STK_RETURN, TRANFERIN, TRANFEROUT, STK_USAGE, STK_ADJ, STK_WASTE, STK_STAFF, STK_GUEST,
BOM, STK_CONV, STK_VAR, TDY_USAGE, STK_CLOSE FROM #TEMP_IBALANCE SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--IBALANCE
--##############

--##############
--IHEADER
--##############
--BEGIN
INSERT INTO IHEADER
(CO_CD,SA_OUT_CD,STK_LOC,POS_NO,STAFF_C_CD,TRN_TYPE,REF_NO,TRN_NO,TRN_DT,BN_DT,GRS_AMT,DISCOUNT,
COST,TAX_AMT_1,TAX_AMT_2,ROUND_AMT,LOC_TO,LOC_FROM,REMARKS,VENDOR_CD)
SELECT CO_CD,SA_OUT_CD,STK_LOC,POS_NO,STAFF_C_CD,TRN_TYPE,REF_NO,TRN_NO,TRN_DT,BN_DT,GRS_AMT,DISCOUNT,
COST,TAX_AMT_1,TAX_AMT_2,ROUND_AMT,LOC_TO,LOC_FROM,REMARKS,VENDOR_CD FROM #TEMP_IHEADER SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--IHEADER
--##############

--##############
--IDETAIL
--##############
--BEGIN
INSERT INTO IDETAIL
(CO_CD,SA_OUT_CD,STK_LOC,REF_NO,TRN_NO,TRN_DT,BN_DT,TRN_TYPE,PROD_CD,PROD_G01,PROD_G02,
PROD_SIZE,PROD_COLOR,PROD_NM,UOM,CONV,SA_QTY,SA_AMOUNT,PROD_COST,DISCOUNT,BARCODE,INV_QTY,
TAX_1,TAX_2,PROD_TYPE,STK_CONTRL)
SELECT CO_CD,SA_OUT_CD,STK_LOC,REF_NO,TRN_NO,TRN_DT,BN_DT,TRN_TYPE,PROD_CD,PROD_G01,PROD_G02,
PROD_SIZE,PROD_COLOR,PROD_NM,UOM,CONV,SA_QTY,SA_AMOUNT,PROD_COST,DISCOUNT,BARCODE,INV_QTY,
TAX_1,TAX_2,PROD_TYPE,STK_CONTRL FROM #TEMP_IDETAIL SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--IDETAIL
--##############

UPDATE IHEADER SET [STATUS]='P'
UPDATE IDETAIL SET [STATUS]='P'
UPDATE IHEADER SET REF_DT=BN_DT where REF_DT is null
UPDATE IDETAIL SET REF_DATE=BN_DT where REF_DATE is null

--##############
--POST STOCK TRANSFER
--##############
EXEC spImportStkTransfer @BN_DT
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--POST STOCK TRANSFER
--##############
--##############
--POST INVENTORY
--##############
--ADDED BY KIT FOR SALES TO SALES ORDER POSTING FOR DMK
--BEGIN
DECLARE @LINK_TYPE VARCHAR(2)
SELECT @LINK_TYPE=UPPER(LINK_TYPE) FROM SA_OUT WHERE SA_OL_CD=@SA_OUTLET_CD
IF @LINK_TYPE='C'
BEGIN
EXEC spSalesToSalesOrder @BN_DT, @SA_OUTLET_CD
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
END
ELSE
BEGIN
EXEC spPostInventory @BN_DT, @SA_OUTLET_CD, @TOTAL_PROCESSED, @FILENAME
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
END
--##############
--POST INVENTORY
--##############
--##############
--INSERT INTO LOG TABLE
--##############
INSERT INTO TRN_POS_LOG
(FILENAME, ADD_DATE, USR_ID, LOG_TYPE, SA_OUTLET_CD)
VALUES
(@FILENAME, GETDATE(), 'AUTO', 'S', @SA_OUTLET_CD)
SET @ERROR_CD=@@ERROR
IF @ERROR_CD<>0
BEGIN
SET @RESULT=@ERROR_CD
ROLLBACK TRAN
RETURN
END
--##############
--INSERT INTO LOG TABLE
--##############



--##############
--ADDITIONAL STEPS
--##############
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='spCustomizedDayend')
BEGIN
EXEC spCustomizedDayend @ACC_SA_OUT_CD, @BN_DT
IF @@ERROR<>0
BEGIN
SET @RESULT=@@ERROR
ROLLBACK TRAN
RETURN
END
END
--##############
--ADDITIONAL STEPS
--##############
COMMIT TRAN
END --END OF IF STATEMENT
GO

 

[nicholasting]

Above is my Stored Procedure inside my SQL server.

i have tried at other machine. the stored procedure is running successfully and also the data from DBF has inserted into SQL server.

can any "anonymous" files make my process failed?

thanks alot for giving me some guides.

regards,
nicholas

 

[SQLDenis]

I don't see where the 3 #TEMP.... tables get populated?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nicholasting]

the temp table is created at previous functions.
the whole process of my module is an exe will taking out the respective DBFs files from a zip file. based on the DBFs, it will create temp tables in SQL server. from the temp table then only insert into sql tables.

after few times i'm checking with my team member, we found out that maybe is some system files facing contain problems.
the 1st time, we were uninstall the sql server and reinstall the sql server. then the import was successfully. we were happy with that.

unluckily, the import is not working again for the next day. then we found out the "comdlg32.ocx" contains different version and sizes. after we replace a new one and register it. the process currently is successfully again. but i not sure for tomorrow or after that.

is it because some system files that belong for windows and SQL server make the import from DBF to SQL cant successfully? the same stored procedure we were applying to another machine, until now we don't have any difficulties of importing process.


thanks

regards,
nicholas