August 7, 2015 at 1:14 pm
Hi folks,
I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:\DataStaging\DWHStaging\Source\Abverkaufsquote\'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''\r''
, codepage = ''1252''
, keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
Can anyone hazard a guess as to why this isn't working as it should?
Regards,
Kev
August 7, 2015 at 1:23 pm
Well...the code you posted doesn't do anything but set some variables. You either need to share the rest of the code, or write that code that executes it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 7, 2015 at 1:28 pm
Here the complete stored procedure:
USE [DWHStaging]
GO
/****** Object: StoredProcedure [dbo].[sp_IMPORT_Abverkaufsquote] Script Date: 07/08/2015 19:00:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_IMPORT_Abverkaufsquote]
as
create table #filelist(pos int identity(1,1), name varchar(max))
insert into #filelist exec xp_cmdshell 'dir G:\DataStaging\DWHStaging\Source\Abverkaufsquote\ /A-D /B'
--select * from #filelist
--DECLARATIONS
declare @fname nvarchar(max)--Name of the CSV file
declare @path nvarchar(max)--Abolute path to the folder containing the files
declare @open nvarchar(max)--Starting statement for the Bulk Insert
declare @params nvarchar(max)--Bulk Insert parameters
declare @filepath nvarchar(max)--Complete abolute path including the filename
declare @fcount int--How any files are in the source folder?
declare @i int--Required for the loop
--CONSTRUCT THE DYNAMIC SQL
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from '''
set @path = 'G:\DataStaging\DWHStaging\Source\Abverkaufsquote\'
set @params = ''' with (firstrow = 2
, datafiletype = ''widechar''
, fieldterminator = '';''
, rowterminator = ''\r''
, codepage = ''1252''
, keepnulls);'
set @fcount = (select count(*) from #filelist where name is not null and name like 'RFA_Abv%')
set @i = 0
--BULK IMPORT ALL FILES AND MOVE TO THE STORE ON SUCCESSFULL COMPLETION
begin try
while (@i < @fcount)
begin
set @fname = (select name from #filelist where name is not null and pos=@i+1)
set @filepath = @path + @fname
declare @sql nvarchar(max) = @open + @filepath + @params
set @i = @i +1
exec sp_executesql @sql
--declare @move varchar(200)= N'move G:\DataStaging\DWHStaging\Source\Abverkaufsquote\' + @fname + ' G:\DataStaging\DWHStaging\Archive\';
exec xp_cmdshell @move;
end
end try
begin catch
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SendMail',
@recipients = *********,
@subject = 'User Intervention Required!',
@body = 'An error has occurred in the run of the Stored Procedure sp_Abverkaufsquote',
@importance = 'high';
end catch
drop table #filelist
I know the code doesn't have any error handling at the moment....one thing at a time!
Thanks for taking the time to look!
Regards,
Kev
August 8, 2015 at 1:40 am
UPDATE:
I have narrowed it down to the BULK INSERT Statement itself:
bulk insert [DWHStaging].[dbo].[v_Abverkaufsquote] from 'G:\DataStaging\DWHStaging\Source\Abverkaufsquote\RFA_Abverkaufsquote_2015-08-07_12.17.48.csv' with (firstrow = 2, datafiletype = 'widechar', fieldterminator = '0x3B', keepnulls, rowterminator = '0x0d0a');
I have tried using the Codepages ACP, OEM and RAW but nothing seems to be able to read the data out for the Import itself.
The procedure I have written (see above) can find the files to be imported, constructs the dynamic SQL correctly and moves the files after the "Import" to the Archive Location. I can only think this is a Problem with the Codepage/collation of the CSV file itself.
Could the Problem lie in the fact the file was extracted from DB2 and something was forgotten?
Regards,
Kev
August 8, 2015 at 3:06 am
UPDATE:
A more detailed error message reads:
Error: 0xC002F304 at Bulk Insert Task, Masseneinfügungstask: Fehler mit folgender Fehlermeldung: 'Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file.Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.'.
Task failed: Bulk Insert Task
So....I changed the datatype to "widechar" and got this:
'The statement has been terminated.Arithmetic overflow error converting expression to data type nvarchar.'
This is confusing me beyond words....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply