Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bulk insert of large files (more then 450MB)


Bulk insert of large files (more then 450MB)

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8247 Visits: 14368
Unfortunately BULK INSERT does not play nice sometimes so we have to apply a workaround. We cannot CATCH errors from BULK INSERT related to gaining exclusive access to the file straightaway. We must use a proc and a nested TRY/CATCH, i.e. two TRY/CATCH blocks and a context switch to catch one error.

A variation of Jeff's solution is the one I would go with.

I would just try loading the file straightaway and catch the exception from the inner proc call. If it is an exception about 'exclusive access' like this ...

Cannot bulk load because the file "%s" could not be opened. Operating system error code 32(The process cannot access the file because it is being used by another process.).

... then wait 5 seconds and try again.

The technique requires no additional features be enabled which helps you on the security and auditing side and while managing program flow using exceptions is generally frowned upon, checking for exclusive file access is actually one of the few scenarios where it is acceptable. How do you know if a file is ready unless you try opening it? And if you simply test for access and proceed if you get exclusive access, by the time you drop into your processing code something else could have grabbed exclusive access in between. Now, is it likely that someone will grab exclusive access after it initially your particular file becomes available? Maybe not in your scenario but it never hurts to use a known design pattern that simply works.

Something like this:

1. create new folder called C:\@
2. save the text file attached to this post to C:\@\
3. Open the text file in Excel to block others from gaining exclusive access to the file.
4. Run this SQL on a local instance:

USE tempdb;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.incoming_data')
AND type IN (N'U') )
DROP TABLE dbo.incoming_data;
GO
CREATE TABLE dbo.incoming_data (identifier VARCHAR(50));
GO

IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.load_data_inner')
AND type IN (N'P', N'PC') )
DROP PROCEDURE dbo.load_data_inner;
GO
CREATE PROC dbo.load_data_inner
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.incoming_data;

BEGIN TRY

BULK INSERT dbo.incoming_data
FROM 'c:\@\1.txt'
WITH
(
FIELDTERMINATOR ='|',
ROWTERMINATOR =''
);

END TRY
BEGIN CATCH
-- notice this code will not be hit when an exclusive access error occurs
RAISERROR('dbo.load_data_inner CATCH',10,1) WITH NOWAIT;
END CATCH

END
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.load_data_outer')
AND type IN (N'P', N'PC') )
DROP PROCEDURE dbo.load_data_outer;
GO
CREATE PROC dbo.load_data_outer
AS
BEGIN
SET NOCOUNT ON;
WHILE 1 = 1
BEGIN

BEGIN TRY
EXEC dbo.load_data_inner;
BREAK;
END TRY
BEGIN CATCH
RAISERROR('dbo.load_data_outer CATCH',10,1) WITH NOWAIT;

DECLARE @error_number INT = ERROR_NUMBER(),
@error_message NVARCHAR(2048)= ERROR_MESSAGE();
RAISERROR('%d',10,1,@error_number) WITH NOWAIT;
RAISERROR('%s',10,1,@error_message) WITH NOWAIT;

IF (
@error_number = 4861
AND @error_message LIKE '%Operating system error code 32(The process cannot access the file because it is being used by another process.).%'
)
BEGIN
RAISERROR('waiting for exclusive access...',10,1) WITH NOWAIT;
WAITFOR DELAY '00:00:05';
END
ELSE
BEGIN
-- something we did not expect happened so bubble up the error, i.e. exit this proc
RAISERROR( @error_message,11,1);
END
END CATCH

END
END
GO

EXEC dbo.load_data_outer
GO

SELECT *
FROM dbo.incoming_data
GO



5. Switch to the messages Tab in the SSMS window to see what the code is doing.
6. When you have had enough close the file from Excel and the code should succeed.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
1.txt (3 views, 9 bytes)
adminorama
adminorama
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 94
Thanks a lot for this powerful alternative, I cannot test it right now but makes perfect sense and I'll surely give it a try soon.
I had a very complete overview of the problems thanks to your and other people's answer, this forum is so good!

Thank you very much
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45145 Visits: 39923
opc.three (9/19/2012)
I would just try loading the file straightaway and catch the exception from the inner proc call. If it is an exception about 'exclusive access' like this ...



+1

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search