[dba-SQLServer] Is there any chance?

jwcolby jwcolby at colbyconsulting.com
Mon May 21 17:17:44 CDT 2012


I am trying to get Bulk Insert (import) working with CSV files ("," delimited) using a format file. 
  I have about 60 files with around 250 million total records that I need to import.  Just using the 
built-in wizard works but is a royal PITA because I have to modify each and every time to widen the 
fields to 250 characters, build each file by hand etc.

So I have a SP that creates the tables.  I then use the following to try to do the import:

USE [_DataEmailDD]
GO
/****** Object:  StoredProcedure [dbo].[sp_AZIn_BCPInOneFile]    Script Date: 05/21/2012 17:19:43 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_AZIn_BCPInOneFile]
	-- Add the parameters for the stored procedure here
	@DBName varchar(50), @TblName varchar(50),
	@FilePath varchar(1000), @FileName varchar(255)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

     -- Insert statements for procedure here
declare @sql varchar(8000)
DECLARE @FileSpec varchar(1000)

Declare	@ErrorDesc varchar(4000)
Declare	@ErrorNo int
Declare	@RecsAffected int

	set @filespec = @FilePath	
	set @filespec = @filespec + @FileName
	begin try
		set @SQL = 'BULK INSERT [' + @DBName + '].[dbo].[' + @TblName + '] FROM '  + '''' + @filespec + 
''''  + ' WITH (FORMATFILE= ' + '''' + '\\Azul\PSM\data\_DataEmailDD\EmailImpFormat.xml' + '''' + ')'
		
		print @SQL
		exec (@SQL)
		select @RecsAffected = @@RowCount
		select @ErrorDesc = 'Success'
		select @ErrorNo = 0
		return 0
	end try	
	begin catch
		select @ErrorNo = @@Error
		select @ErrorDesc = ERROR_MESSAGE()
		print 'There was a BCP error IMPORTING data into ' + @DBName
		print ERROR_MESSAGE()
		return -1
	end catch
	--

Standard stuff except for the SQL Statement which when printed gives me the following:

BULK INSERT [_DataEmailDD].[dbo].[ConsEmail_AK] FROM '\\Azul\PSM\Data\_DataEmailDD\ConsEmail_AK.CSV' 
WITH (FORMATFILE= '\\Azul\PSM\data\_DataEmailDD\EmailImpFormat.xml')

and execution thereof gives me the infamous (and totally useless)

There was a BCP error IMPORTING data into _DataEmailDD
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

It just seems like my chance of making this work is pretty minimal.

I have spent about 4 hours Googling to even get as far as I am and the

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

returns about a million entirely different pages for a million entirely different problems.  It is 
as if any problem whatsoever returns this message.

So, what do you say?  Is this a "you gotta be an expert so forget it" kinda deal?  All of the 
various web pages explaining how to do this make it sound so easy but of course no one mentions how 
to troubleshoot the mess.

-- 
John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it



More information about the dba-SQLServer mailing list