March 11, 2002 at 1:05 pm
I am trying to convert a series of processes currently using BCP, into Bulk Insert on a SQL7 server with SP1. However, I seem to be having issues trapping error conditions.
The code below works fine if there are no errors, but if the file does not exist or the format of the file is wrong, the error code is returned in the Query Analyser window but the remaining TSQL code (to return the error condition) does not run? I have tried this as a stored procedure with the same results. Is it me, or is the error handling of Bulk Insert different?
DECLARE @err_result smallint, @filename varchar(255)
select @filename = '23360001.csv'
EXEC ('BULK INSERT ABPCompany.dbo.oeimphdr_bcp
FROM "C:\EDI\Despatch\Imports\ELSales\' + @filename + '"
WITH
(
DATAFILETYPE = "char",
FIELDTERMINATOR = ",",
LASTROW = 1
)')
select @err_result = @@error
IF @err_result <> 0
SELECT @err_result
ELSE
SELECT 0
GO
March 11, 2002 at 1:49 pm
Use bcp with the /e errfile command switch. This will allow you to specify a file with path for errors to be wrtten to then you can go back and review what bcp was barking about.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 12, 2002 at 3:44 am
At present I am not bothered what the error code is, I am just trying to trap if there is one.
March 12, 2002 at 5:03 pm
First I believe when you perform EXEC ('anythinghere') the anythinghere part actually runs outside th scope of the current code (like it was written as a seperate unrelated piece of code. Try this,
DECLARE @err_result smallint, @filename varchar(255)
--select @filename = '23360001.csv'
BEGIN
BULK INSERT ABPCompany.dbo.oeimphdr_bcp
FROM "C:\EDI\Despatch\Imports\ELSales\23360001.csv"
WITH
(
DATAFILETYPE = "char",
FIELDTERMINATOR = ",",
LASTROW = 1
)')
select @err_result = @@error
IF @err_result <> 0
SELECT @err_result
ELSE
SELECT 0
END
GO
May not be exactly right as I don't have a way to test right off.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 4:42 am
I am experiencing the same problem when a format error occurs in the file. I have tried the following code but the error is still not trapped due the code not executing after the Bulk Insert failing.
DECLARE @err_result smallint
BEGIN
BULK INSERT ABB_DW_DEV..IMP_HED FROM 'D:\ABBDW\IMPORT\DATA\Transfer\VAX\Import_Head.Txt'
WITH (FORMATFILE = 'D:\ABBDW\IMPORT\FORMAT\Imp_Hed.Fmt',
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = 'r\n')
select @err_result = @@error
IF @err_result <> 0
SELECT @err_result
ELSE
SELECT 0
END
Errors reported:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Thanks in advance
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply