December 13, 2011 at 1:39 am
Below is part of a stored proc. If the Bulk insert fails will it run the get sequence. What can you put after a Exec to say if this fails stop..??
BULK
INSERT UDEF_WEB_ORDER_TEMP_0001
FROM 'D:\WebFiles\Web_File_Order_In\RORD0001.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
waitfor delay '00:00:01'
declare @sop_order_id varchar(100)
exec AA_GET_SEQUENCE_NO_S 'SOP_ORDER_ID', 1, @sop_order_id OUT
insert into sop_trn_temp
(
SOP_ORDER_ID,--1
SOP_STATUS_TEMP,--2
SOP_SOURCE,--3
SOP_HEADER_STATUS,--4
SOP_ACCOUNT,--5
SOP_CUSTOMER_REF,--6
SOP_DELIVERY_ADDRESS_NO,--7
SOP_INVOICE_ADDRESS_NO,--8
--SOP_BATCH_REF,--9
SOP_ORDER_DESCRIPTION,--10
SOP_ORDER_INTERNAL_NOTES,--11
--SOP_DELIVERY_CHARGE,--12
--SOP_DATE_REQUIRED,--13
SOP_ENTRYTYPE,--14
SOP_STOCK_CODE,--15
--SOP_LINE_DETAIL,--16
SOP_RESERVE_STK,--17
SOP_ORDER_LINE_NO,--18
SOP_QTY_ORDERED,--19
SOP_UNITCOST,--20
SOP_SUB_ANALYSIS,--21
SOP_USER_ID--22
)
(select
@SOP_ORDER_ID,--SOP_ORDER_ID,--1
0,--SOP_STATUS_TEMP,--2
'A',--SOP_SOURCE,--3
'Full Process',--SOP_HEADER_STATUS,--4
CustomerCode,--SOP_ACCOUNT,--5
'TEST',--SOP_CUSTOMER_REF,--6
1,--SOP_DELIVERY_ADDRESS_NO,--7
1,--SOP_INVOICE_ADDRESS_NO,--8
--'',--SOP_BATCH_REF,--9
'WEB ORDER-'+OrderCode,--SOP_ORDER_DESCRIPTION,--10
'TEST',--SOP_ORDER_INTERNAL_NOTES,--11
--'',--SOP_DELIVERY_CHARGE,--12
--'',--SOP_DATE_REQUIRED,--13
'S',--SOP_ENTRYTYPE,--14
ProductCode,--SOP_STOCK_CODE,--15
--'',--SOP_LINE_DETAIL,--16
'1',--SOP_RESERVE_STK,--17
OrderLineNumber,--SOP_ORDER_LINE_NO,--18
Quantity,--SOP_QTY_ORDERED,--19
'0.99',--SOP_UNITCOST,--20
'RM18',--SOP_SUB_ANALYSIS,--21
'WEB'--SOP_USER_ID--22
from UDEF_WEB_ORDER_TEMP_0001)
exec aa_process_sop_trn_temp_s
@ps_trn_temp_id = @sop_order_id,
@ps_user_id = 'WEB',
@ps_proccess_status = 2
December 13, 2011 at 2:31 am
Try this:
BULK
INSERT UDEF_WEB_ORDER_TEMP_0001
FROM 'D:\WebFiles\Web_File_Order_In\RORD0001.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
SELECT @Error = @@ERROR
IF @Error = 0 /* No error found*/
BEGIN
-----------YourCodeToBeExecutedHere-----------
END
ELSE
BEGIN
RAISERROR ('Some error occurred',16,1)
END
Note:
1. Waitfor delay has been removed because @@Error catches the error returned by immediate last statement executed.
2. You can use TRY...CATCH block also. You can achieve same result with them.
December 13, 2011 at 2:49 am
Execellent!! Thanks very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply