SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Error in Bulk Load
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
74 Posts

Posted - 09/05/2013 :  13:57:24  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

415 Posts

Posted - 09/05/2013 :  14:36:56  Show Profile  Reply with Quote
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 http://technet.microsoft.com/en-us/library/ms188365.aspx, especially the section with ERRORFILE.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000