SQL Error in Bulk Load - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Error in Bulk Load

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-09-05 : 13:57:24

I have this SP that someone created that is no longer with the company. When I run this SP, I am getting this error. I am not sure how to fix the issue. Please help, Thanks.

Here is the error I am getting......

Msg 4861, Level 16, State 1, Procedure p_BulkInsertDevTable, Line 108
Cannot bulk load because the file "C:\IMPACT_DATA\PROD\dev.err" could not be opened. Operating system error code 80(The file exists.).
Msg 4861, Level 16, State 1, Procedure p_BulkInsertDevTable, Line 108
Cannot bulk load because the file "C:\IMPACT_DATA\PROD\dev.err.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

Here is the Store Procedure
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Impact SQL Server-Dump
declare @err int, @current_table varchar(25), @end_rowcount int
select @current_table='dev'
PRINT 'Attempting to drop dev table.'

-- Table: dev
if exists (select * from sysobjects where name='dev' and xtype='U') drop table [dev]
select @err = @@error
if @err <> 0
BEGIN
PRINT 'Error ' + ltrim(str(@err)) + ' occurred while attempting to drop ' + ltrim(@current_table) + '.'
INSERT INTO impactLog (logType, logMessage, logTable, logErrorNum) VALUES ('ERROR', ltrim(str(@err)) + ' occurred while attempting to drop ' + ltrim(@current_table) + '.', @current_table, @err)
END
PRINT 'Attempting to create [dev] table.'
CREATE TABLE [dev] (
dev_id1 varchar(8) NOT NULL default '',
dev_snapdt datetime,
dev_recid1 varchar(8) default '',
dev_extkey varchar(60) default '',
dev_rptdt varchar(8) default '',
dev_newdt datetime,
dev_newby varchar(6) default '',
dev_chgdt datetime,
dev_chgby varchar(6) default '',
dev_xtra varchar(80) default '',
dev_upda datetime,
dev_eff datetime,
dev_trm datetime,
dev_ahs varchar(1) default '',
dev_npi varchar(10) default '',
dev_id varchar(50) default '',
dev_upin varchar(12) default '',
dev_lname varchar(50) default '',
dev_fname varchar(50) default '',
dev_minit varchar(1) default '',
dev_ext varchar(5) default '',
dev_degr varchar(10) default '',
dev_ssn varchar(12) default '',
dev_ment varchar(1) default '',
dev_specp varchar(1) default '',
dev_mva varchar(10) default '',
dev_wc varchar(10) default '',
dev_spec1 varchar(30) default '',
dev_spec2 varchar(30) default '',
dev_spec3 varchar(30) default '',
dev_taxid varchar(9) default '',
dev_pract varchar(35) default '',
dev_addr1 varchar(75) default '',
dev_addr2 varchar(55) default '',
dev_city varchar(25) default '',
dev_zip varchar(10) default '',
dev_state varchar(2) default '',
dev_phn varchar(12) default '',
dev_fax varchar(12) default '',
dev_billnm varchar(50) default '',
dev_baddr1 varchar(50) default '',
dev_bcity varchar(25) default '',
dev_bzip varchar(10) default '',
dev_bstate varchar(2) default '',
dev_bphn varchar(12) default '',
dev_hos1 varchar(50) default '',
dev_hos2 varchar(50) default '',
dev_hos3 varchar(50) default '',
dev_hos4 varchar(50) default '',
dev_hos5 varchar(50) default '',
dev_county varchar(25) default '',
dev_fee varchar(6) default '',
dev_wcfee varchar(6) default '',
dev_lat varchar(8) default '',
dev_lon varchar(8) default '',
dev_url varchar(24) default '',
dev_email varchar(60) default '',
dev_radmod varchar(50) default '',
dev_sex varchar(1) default '',
dev_dob datetime,
dev_sys varchar(30) default '',
dev_medlic varchar(20) default '',
dev_xtyp varchar(4) default '',
dev_lang1 varchar(30) default '',
dev_lang2 varchar(30) default '',
dev_lang3 varchar(30) default '',
dev_lang4 varchar(30) default '',
dev_lang5 varchar(30) default '',
dev_fill varchar(MAX) default '',
PRIMARY KEY (dev_id1)
)
select @err = @@error
if @err <> 0
BEGIN
PRINT 'Error ' + ltrim(str(@err)) + ' occurred while creating table ' + ltrim(@current_table) + '.'
INSERT INTO impactLog (logType, logMessage, logTable, logErrorNum) VALUES ('ERROR', ltrim(str(@err)) + ' occurred while creating table ' + ltrim(@current_table) + '.', @current_table, @err)
END

PRINT 'BULK INSERT'

BULK INSERT [dev]
FROM 'C:\IMPACT_DATA\PROD\dev.dat'
WITH (ROWTERMINATOR='\n', FIELDTERMINATOR = ',', MAXERRORS=99999999, ERRORFILE ='C:\IMPACT_DATA\PROD\dev.err')

select @err = @@error
if @err <> 0
BEGIN
PRINT 'Error ' + ltrim(str(@err)) + ' occurred while bulk inserting data into ' + ltrim(@current_table) + '.'
INSERT INTO impactLog (logType, logMessage, logTable, logErrorNum) VALUES ('ERROR', ltrim(str(@err)) + ' occurred while bulk inserting data into ' + ltrim(@current_table) + '.', @current_table, @err)
END

PRINT 'CREATE INDEX IDX2_dev'
--declare @err int, @current_table varchar(25), @end_rowcount int
CREATE INDEX IDX2_dev ON [dev] (dev_extkey, dev_id1) WITH (FILLFACTOR = 90);
select @err = @@error
if @err <> 0
BEGIN
PRINT 'Error ' + ltrim(str(@err)) + ' occurred while creating IDX2 index on ' + ltrim(@current_table) + '.'
INSERT INTO impactLog (logType, logMessage, logTable, logErrorNum) VALUES ('ERROR', ltrim(str(@err)) + ' occurred while creating IDX2 index on ' + ltrim(@current_table) + '.', @current_table, @err)
END

PRINT 'declare @err int'
--declare @err int, @current_table varchar(25), @end_rowcount int
CREATE INDEX IDX_AX1_dev ON [dev] (dev_snapdt, dev_recid1, dev_extkey, dev_rptdt, dev_npi, dev_id, dev_lname, dev_fname, dev_taxid) WITH (FILLFACTOR = 90);
select @err = @@error
if @err <> 0
BEGIN
PRINT 'Error ' + ltrim(str(@err)) + ' occurred while creating AX1 index on ' + ltrim(@current_table) + '.'
INSERT INTO impactLog (logType, logMessage, logTable, logErrorNum) VALUES ('ERROR', ltrim(str(@err)) + ' occurred while creating AX1 index on ' + ltrim(@current_table) + '.', @current_table, @err)
END

END

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-05 : 14:36:56

Someone did run your SP, and it failed upon bulk insert, due to formatting errors in the file dev.dat.
Now, the bulk insert has created the file dev.err to indicate the bulk insert has failed. It has also created the file dev.err.Error.txt and put the rows with the formatting errors in.

So you need to look in the file dev.err.Error.txt to see wich rows has errors, and correct them in the dev.dat file. Then delete the files dev.err and dev.err.Error.txt and run the SP again.

Read about the bulk insert [url]http://technet.microsoft.com/en-us/library/ms188365.aspx[/url], especially the section with ERRORFILE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources